触发器的类型

定时

有两个触发动作时间修饰符:

  • BEFORE 触发器在执行请求之前激活,
  • AFTER 在变化后引发火灾。

触发事件

触发器有三个事件可以附加到:

  • INSERT
  • UPDATE
  • DELETE

在 Insert 触发器示例之前

DELIMITER $$

CREATE TRIGGER insert_date 
    BEFORE INSERT ON stack
    FOR EACH ROW
BEGIN
        -- set the insert_date field in the request before the insert
    SET NEW.insert_date = NOW();
END;

$$
DELIMITER ;

在 Update 触发示例之前

DELIMITER $$

CREATE TRIGGER update_date 
    BEFORE UPDATE ON stack
    FOR EACH ROW
BEGIN
        -- set the update_date field in the request before the update
    SET NEW.update_date = NOW();
END;

$$
DELIMITER ;

删除触发器示例后

DELIMITER $$

CREATE TRIGGER deletion_date 
    AFTER DELETE ON stack
    FOR EACH ROW
BEGIN
        -- add a log entry after a successful delete
    INSERT INTO log_action(stack_id, deleted_date) VALUES(OLD.id, NOW());
END;

$$
DELIMITER ;