MySQL学习之触发器

触发器

如果你想在对表进行增(insert)、删(delete)、改(update)操作的前(before)后(after)

触发某种特定的行为的时候，就可以使用触发器 （没有查select）

创建

# 创建：
#       插入前
        CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
        BEGIN
            ...
        END

        # 插入后
        CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
        BEGIN
            ...
        END

        # 删除前
        CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
        BEGIN
            ...
        END

        # 删除后
        CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
        BEGIN
            ...
        END

        # 更新前
        CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
        BEGIN
            ...
        END

        # 更新后
        CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
        BEGIN
            ...
        END


        delimiter //
        CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
        BEGIN
            IF NEW.success = 'no' THEN #等值判断只有一个等号
                INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
            END IF ; #必须加分号
        END//

        delimiter ;

        # insert 只有NEW
        # delete 只有OLD
        # update 有NEW、OLD

删除

drop trigger tri_before_insert_cmd;