第13章 触发器和事件

第13章触发器和事件学习目标：了解触发器的基本概念，掌握触发器的创建、查看和删除的基本操作方法；了解事件的基本概念，掌握事件的创建、查看和管理的基本操作方法。13.1触发器13.1.1触发器的基本概念1．触发器的概念2．触发器的事件类型3．触发器的用途13.1触发器13.1.2创建触发器CREATETRIGGERtrigger_name{BEFORE|AFTER}{INSERT|UPDATE|DELETE}ONtable_nameFOREACHROW[trigger_order]trigger_body;13.1触发器【例13-1】在student_db数据库中，创建一个用户表和一个用户操作日志表。要求当在user表中添加一行记录时，先自动在user_log表中添加日志记录。USEstudent_db;DROPTABLEIFEXISTSuser;CREATETABLEuser(uidINTPRIMARYKEYAUTO_INCREMENT,usernameVARCHAR(50)NOTNULL,passwordVARCHAR(50)NOTNULL);13.1触发器DROPTABLEIFEXISTSuser_log;CREATETABLEuser_log(idintPRIMARYKEYAUTO_INCREMENT,timeTIMESTAMP,log_textVARCHAR(100));13.1触发器2）DROPTRIGGERIFEXISTStri_test1;CREATETRIGGERtri_test1BEFOREINSERTONuserFOREACHROWINSERTINTOuser_logVALUES(NULL,NOW(),'newline');13.1触发器3）INSERTINTOuserVALUES(101,'aaa','111');4）SELECT*FROMuser;

SELECT*FROMuser_log;13.1触发器13.1.3关键字OLD和NEW1．关键字OLD和NEW的访问方式（1）INSERT触发器（2）UPDATE触发器（3）DELETE触发器13.1触发器2．访问触发器NEW表和OLD表的语法OLD.column_nameNEW.column_name13.1触发器【例13-2】NEW和OLD的使用，仍然在user表和user_log表中操作。1）INSERT触发器。DROPTRIGGERIFEXISTStri_test2;CREATETRIGGERtri_test2AFTERINSERTONuserFOREACHROWINSERTINTOuser_logVALUES(NULL,NOW(),CONCAT('新用户为:',NEW.username,NEW.password));INSERTINTOuserVALUES(102,'bbb','222');SELECT*FROMuser;

SELECT*FROMuser_log;13.1触发器2）UPDATE触发器。①使用OLD关键字。DROPTRIGGERIFEXISTStri_test3;CREATETRIGGERtri_test3AFTERUPDATEONuserFOREACHROWINSERTINTOuser_logVALUES(NULL,NOW(),CONCAT('修改用户,旧记录是：',OLD.uid,OLD.username,OLD.password));UPDATEuserSETpassword='222222'WHEREuid=102;SELECT*FROMuser;

SELECT*FROMuser_log;13.1触发器②使用NEW关键字。DROPTRIGGERIFEXISTStri_test4;CREATETRIGGERtri_test4AFTERUPDATEONuserFOREACHROWINSERTINTOuser_logVALUES(NULL,NOW(),CONCAT('修改用户,新记录是：',NEW.uid,NEW.username,NEW.password));UPDATEuserSETpassword='111111'WHEREuid=101;查看user表和user_log表中的记录，SQL语句和运行结果如下：SELECT*FROMuser;

SELECT*FROMuser_log;13.1触发器3）DELETE触发器。DROPTRIGGERIFEXISTStri_test5;CREATETRIGGERtri_test5AFTERDELETEONuserFOREACHROWINSERTINTOuser_logVALUES(NULL,NOW(),CONCAT('删除用户,删除记录为:',OLD.uid,OLD.username,OLD.password));DELETEFROMuserWHEREuid=102;查看user表和user_log表中的记录，SQL语句和运行结果如下：SELECT*FROMuser;

SELECT*FROMuser_log;13.1触发器13.1.4查看触发器1．查看一个或多个触发器（1）查看所有触发器SHOWTRIGGERS[{FROM|IN}db_name];【例13-3】查看student_db数据库中已经存在的所有触发器。SQL语句和运行结果如下：USEstudent_db;SHOWTRIGGERS;13.1触发器（2）查看某个触发器SHOWTRIGGERSWHERE`TRIGGER`LIKE'trigger_name';例如，查看触发器tri_test3，SQL语句如下：SHOWTRIGGERSWHERE`TRIGGER`LIKE'tri_test3';13.1触发器2．查看触发器的详细信息SELECT*FROMinformation_schema.triggers[WHERETRIGGER_NAME='trigger_name'];【例13-4】使用SELECT语句查询triggers表中的信息。SELECT*FROMinformation_schema.triggers;SELECT*FROMinformation_schema.triggersWHERETRIGGER_NAME='tri_test3';13.1触发器13.1.5删除触发器DROPTRIGGER[IFEXISTS][db_name.]trigger_name;【例13-5】删除student_db数据库中的触发器tri_test1和tri_test2。DROPTRIGGERIFEXISTStri_test1;DROPTRIGGERIFEXISTStri_test2;SHOWTRIGGERS;13.1触发器13.1.6使用触发器的注意事项和执行顺序以下内容见二维码。1）使用触发器的注意事项。2）触发器的6种形式。3）触发器的执行顺序和应用实例。4）触发器发生错误时的处理方式。13.1触发器13.1.7使用触发器的实例1．BEFOREINSERT触发器的使用方法2．AFTERINSERT触发器的使用方法3．BEFOREUPDATE触发器的使用方法4．AFTERUPDATE触发器的使用方法5．BEFOREDELETE触发器的使用方法6．AFTERDELETE触发器的使用方法13.2事件13.2.1概述1．事件的概念2．查看事件调度器SHOWVARIABLESLIKE'event_scheduler';

SELECT@@event_scheduler;

3．开启事件调度器SETGLOBALevent_scheduler=ON;SET@@GLOBAL.event_scheduler=ON;SETGLOBALevent_scheduler=ON;13.2.2创建事件CREATEEVENT[IFNOTEXISTS]event_nameONSCHEDULEschedule[ONCOMPLETION[NOT]PRESERVE][{ENABLE|DISABLE|DISABLEONSLAVE}][COMMENT'comment']DOevent_body;

AT子句。设置事件在某个时刻发生，用来设置单次的事件。AT子句的语法格式：ATtimestamp[+INTERVALinterval]…13.2事件interval参数值的语法格式：quantity{YEAR|QUARTER|MONTH|DAY|HOUR|MINUTE|WEEK|SECOND|YEAR_MONTH|DAY_HOUR|DAY_MINUTE|DAY_SECOND|HOUR_MINUTE|HOUR_SECOND|MINUTE_SECOND}EVERYinterval[STARTStimestamp[+INTERVALinterval]…][ENDStimestamp[+INTERVALinterval]…]13.2事件13.2.3创建事件的实例1．创建在某个时刻发生的事件（1）常用发生事件的时刻1）立即执行，AT子句如下：ATCURRENT_TIMESTAMP或ATNOW()2）当前时刻30秒后启动事件，AT子句如下：ATCURRENT_TIMESTAMP+INTERVAL30SECOND3）当前时刻的3天后执行，AT子句如下：ATCURRENT_TIMESTAMP+INTERVAL3DAY4）2023年10月22日12点整执行，AT子句如下：ATTIMESTAMP'2023-10-2212:00:00'13.2事件【例13-6】创建一个现在立即执行的事件ev_create_user，事件执行创建一个表user_test。USEstudent_db;CREATEEVENTev_create_userONSCHEDULEATCURRENT_TIMESTAMPDOBEGINDROPTABLEIFEXISTSuser_test; #若存在user_test表则删除该表

CREATETABLEuser_test(u_idBIGINTNOTNULLAUTO_INCREMENTCOMMENT'用户编号',u_nameCHAR(10)COMMENT'用户名',u_passwordCHAR(32)COMMENT'密码',create_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPCOMMENT'创建时间',update_timeTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT'更新时间',PRIMARYKEY(u_id))COMMENT='用户表';END;13.2事件【例13-7】创建一个事件ev_insert_user30，30秒后启动事件，向user_test表中插入一行记录。CREATEEVENTev_insert_user30ONSCHEDULEATCURRENT_TIMESTAMP+INTERVAL30SECONDDOINSERTINTOuser_test(u_name,u_password)VALUES('aaa','111');SELECT*FROMuser_test;13.2事件2．创建在指定区间周期性发生的事件（1）常用的时间间隔每10秒执行，EVERY子句如下：EVERY10SECOND每分钟执行，EVERY子句如下：EVERY1MINUTE每天定时执行，EVERY子句如下：EVERY1DAY每天凌晨1点执行，EVERY子句如下：EVERY1DAYSTARTSDATE_ADD(DATE_ADD(CURDATE(),INTERVAL1DAY),INTERVAL1HOUR)3天后开启每天定时执行，EVERY子句如下：EVERY1DAYSTARTSCURRENT_TIMESTAMP+INTERVAL3DAY每天定时执行，3天后停止执行，EVERY子句如下：EVERY1DAYENDSCURRENT_TIMESTAMP+INTERVAL3DAY13.2事件13.2事件（2）应用实例【例13-8】创建事件ev_insert_user10，每10秒向user_test表中插入一行记录，该事件开始于30秒后，并且在指定的时间（如2033-10-2316:30:00）结束。DROPEVENTIFEXISTSev_insert_user10; CREATEEVENTev_insert_user10ONSCHEDULEEVERY10SECONDSTARTSCURDATE()+INTERVAL30SECONDENDS'2033-10-2316:30:00'DOBEGINIFCURDATE()<'2033-10-2316:30:00'THENINSERTINTOuser_test(u_name,u_password)VALUES('abc','123');ENDIF;END;SELECT*FROMuser_test;13.2事件13.2.4查看事件1．查看所有事件（1）使用SHOWEVENTS语句查看事件SHOWEVENTS;【例13-9】使用SHOWEVENTS语句查看当前数据库中存在的所有事件。SHOWEVENTS;（2）使用系统数据库information_schema中的events表查看事件的详细信息SELECT*FROMinformation_schema.events[WHEREeventname='event_name'];13.2事件【例13-10】使用SELECT语句查询系统数据库information_schema中ev_auto_total事件的详细信息。SQL语句和运行结果如下：SELECT*FROMinformation_schema.eventsWHEREevent_name='ev_auto_total';13.2事件2．查看事件创建信息SHOWCREATEEVENTevent_name;【例13-11】查看事件ev_insert_user10的创建信息。SHOWCREATEEVENTev_auto_total;