MySQL NOTE: triggers

First, the basic knowledge

A trigger is a specific action triggered by an event such as INSERT, UPDATE, and delete.

When trigger conditions are met, the database system executes the program statements defined in the trigger.

Second, create

The for every row indicates that an operation on any one record satisfies the triggering event to be triggered.

MySQL defaults to the end of a semicolon as the execution statement, and a semicolon is required during the creation of the trigger. In order to resolve this problem, you need to use the delimiter statement to turn the Terminator into another symbol, and then change the Terminator back to the semicolon after creation is complete.

CREATE DATABASEMYDB; UseMYDB;CREATE TABLESTUDENT ( Number INT       not NULL PRIMARY KEY, NAMECHAR( -) not NULL);CREATE TABLEInsert_timeline (Insert_timeDATETIME  not NULL);CREATE TABLEDelete_timeline (Delete_timeDATETIME  not NULL);CREATE TRIGGERInsert_trigger AfterINSERT   onSTUDENT forEach ROWINSERT  intoInsert_timelineVALUES(now ()); #创建一个执行语句的触发器DELIMITER&&CREATE TRIGGERDelete_trigger AfterDELETE   onSTUDENT forEach ROWBEGIN    INSERT  intoDelete_timelineVALUES(now ()); INSERT  intoDelete_timelineVALUES(now ()); END &&DELIMITER; #创建多个执行语句的触发器DROP DATABASEMYDB;

View Code

Third, view

SHOW TRIGGERS; #无法查询指定触发器SELECT * frominformation_schema. TRIGGERS; #查询所有触发器SELECT * frominformation_schema. TRIGGERSWHERETrigger_name= 'Insert_trigger'; #查询指定触发器SELECT * frominformation_schema. TRIGGERSWHERETrigger_name= 'Delete_trigger';

View Code

Iv. deletion

DROP TRIGGER Insert_trigger; DROP TRIGGER Delete_trigger;

View Code

MySQL NOTE: triggers