mysql trigger

最新推荐文章于 2024-09-13 22:15:00 发布

qq_23586469

最新推荐文章于 2024-09-13 22:15:00 发布

阅读量166

点赞数

文章标签： mysql trigger

本文链接：https://blog.csdn.net/qq_23586469/article/details/41447651

版权

1、创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

■ trigger_name：标识触发器名称

■ trigger_time：标识触发时机，用before和after替换；

■ trigger_event：标识触发事件，用insert，update和delete替换；

■ tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；

■ trigger_stmt：触发器程序体。通过使用BEGIN ... END结构，能够定义执行多条语句的触发程序。

2、查看触发器

SHOW TRIGGERS

SHOW TRIGGERS from dbname like "aaa%";

information_schema.TRIGGERS

3、删除触发器
DROP TRIGGER [schema_name.]trigger_name

4、其他

OLD关键字和NEW关键字。OLD和NEW可以引用触发器所在表的某一列

对于具有相同触发程序动作时间和事件的给定表，不能有两个触发程序。

通过使用BEGIN ... END结构，能够定义执行多条语句的触发程序。

定义执行多条语句的触发程序时，如果使用mysql程序来输入触发程序，需要重新定义语句分隔符，以便能够在触发程序定义中使用字符“;”。

5、例子

drop trigger if exists crm_callback_trigger_after_insert;
delimiter //
create trigger crm_callback_trigger_after_insert after insert on crm_callback
    for each row
    begin
      select max(service_date) into @max_service_date from crm_callback where tid = new.tid and account_id = new.account_id and contact_id = new.contact_id;
       update task_contact set max_service_date = @max_service_date where tid = new.tid and aid = new.account_id and cid = new.contact_id;
    end;//
delimiter ;

drop trigger if exists crm_callback_trigger_after_update;
delimiter //
create trigger crm_callback_trigger_after_update after update on crm_callback
    for each row
    begin
      select max(service_date) into @max_service_date from crm_callback where tid = new.tid and account_id = new.account_id and contact_id = new.contact_id;
       update task_contact set max_service_date = @max_service_date where tid = new.tid and aid = new.account_id and cid = new.contact_id;
    end;//
delimiter ;

drop trigger if exists crm_callback_trigger_after_delete;
delimiter //
create trigger crm_callback_trigger_after_delete after delete on crm_callback
    for each row
    begin
      select max(service_date) into @max_service_date from crm_callback where tid = old.tid and account_id = old.account_id and contact_id = old.contact_id;
       update task_contact set max_service_date = @max_service_date where tid = old.tid and aid = old.account_id and cid = old.contact_id;
    end;//
delimiter ;