DB 触发器trigger

触发器（trigger）示例

当向tb_policy插数据之后，或者删除数据之后，触发对应操作

DELIMITER $$
USE `hesui`$$
START TRANSACTION$$
DROP TRIGGER IF EXISTS trigger_after_tb_policy_insert $$
CREATE TRIGGER trigger_after_tb_policy_insert AFTER INSERT ON tb_policy FOR EACH ROW
BEGIN
    DECLARE splitChar VARCHAR(2) DEFAULT ';';
    DECLARE sender_except LONGTEXT DEFAULT '';
    DECLARE rcpt_except LONGTEXT DEFAULT '';
    DECLARE tmp VARCHAR(255) DEFAULT '';
    DECLARE cnt INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    SET sender_except = NEW.sender_except;
    SET rcpt_except = NEW.rcpt_except;
    if sender_except IS NOT NULL AND sender_except != '' then
        SET cnt = 1+(LENGTH(sender_except) - LENGTH(REPLACE(sender_except,splitChar,''))); 
        while i < cnt
        DO
            SET i = i + 1;
            SET tmp = reverse(substring_index(reverse(substring_index(sender_except,splitChar,i)),splitChar,1));
            if tmp IS NOT NULL AND tmp != '' then
                INSERT INTO tb_policy_target_exception(policy_id, sender_except, rcpt_except) VALUES (NEW.policy_id, tmp, '*');
            END if;
        END while;
        SET cnt = 0;
        SET i = 0;
        SET tmp = '';
    END if;
    if rcpt_except IS NOT NULL AND rcpt_except != '' then
        SET cnt = 1+(LENGTH(rcpt_except) - LENGTH(REPLACE(rcpt_except,splitChar,''))); 
        while i < cnt
        DO
            SET i = i + 1;
            SET tmp = reverse(substring_index(reverse(substring_index(rcpt_except,splitChar,i)),splitChar,1));
            if tmp IS NOT NULL AND tmp != '' then
                INSERT INTO tb_policy_target_exception(policy_id, sender_except, rcpt_except) VALUES (NEW.policy_id, '*', tmp);
            END if;
        END while;
        SET cnt = 0;
        SET i = 0;
        SET tmp = '';
    END if;
END$$
DROP TRIGGER IF EXISTS trigger_after_tb_policy_delete $$
CREATE TRIGGER trigger_after_tb_policy_delete AFTER DELETE ON tb_policy FOR EACH ROW
BEGIN
    DELETE FROM tb_ldap_group_policy_relation WHERE policy_id = OLD.policy_id;
    DELETE FROM tb_policy_target_exception WHERE policy_id = OLD.policy_id;
END$$
DROP TRIGGER IF EXISTS trigger_before_tb_ldap_group_policy_relation_insert $$
CREATE TRIGGER trigger_before_tb_ldap_group_policy_relation_insert BEFORE INSERT ON tb_ldap_group_policy_relation FOR EACH ROW
BEGIN
    DECLARE exception_id INT DEFAULT 0;
    DECLARE direction INT DEFAULT 0;
    if NEW.exception_id IS NULL and NEW.include = 0 then
        SELECT direction FROM tb_policy WHERE policy_id = NEW.policy_id INTO direction;
        if direction = 0 then
            INSERT INTO tb_policy_target_exception(policy_id, sender_except) VALUES (NEW.policy_id, '*');
        ELSE
            INSERT INTO tb_policy_target_exception(policy_id, rcpt_except) VALUES (NEW.policy_id, '*');
        END if;
        SELECT MAX(id) FROM tb_policy_target_exception INTO exception_id;
        SET NEW.exception_id = exception_id;
    END if;
END$$
COMMIT$$
DELIMITER ;