mysql的trigger创建when_创建mysql触发器

Summary: in this tutorial, you will learn how to createtrigger in MySQLby using theCREATE TRIGGERstatement.

You should follow theintroduction to SQL triggersand trigger implementation in MySQLfirst before going forward with this tutorial.

MySQL trigger syntax

In order to create a trigger you use theCREATE TRIGGERstatement. The following illustrates the syntax of theCREATE TRIGGERstatement:CREATETRIGGERtrigger_nametrigger_timetrigger_eventONtable_nameFOREACHROWBEGIN...END

Let’s examine the syntax above in more detail.You put the trigger name after theCREATE TRIGGERstatement. The trigger name should follow the naming convention[trigger time]_[table name]_[trigger event], for examplebefore_employees_update.

Trigger activation time can beBEFOREorAFTER. You must specify the activation time when you define a trigger. You useBEFOREkeyword if you want to process action prior to the change is made on the table andAFTERif you need to process action after the change is made.

Trigger event can beINSERT,UPDATEorDELETE. This event causes trigger to be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, you have to define multiple triggers, one for each event.

A trigger must be associated with a specific table. Without a table trigger would not exist therefore you have to specify the table name after theONkeyword.

The SQL statements are placed betweenBEGINandENDblock.

TheOLDandNEWkeywords are very handy. TheOLDkeyword refers to the existing record before you change the data and theNEWkeyword refers to the new row after you change the data.

MySQL trigger example

Let’s start creating a trigger in MySQL to audit the changes of theemployeestable.

First, we haveemployeestable in our MySQL sample database as follows:

Second, we create a new table namedemployees_auditto keep the changes of the employee records. The following script creates theemployee_audittable.CREATETABLEemployees_audit(idint(11)NOTNULLAUTO_INCREMENT,employeeNumberint(11)NOTNULL,lastnamevarchar(50)NOTNULL,changedondatetimeDEFAULTNULL,actionvarchar(50)DEFAULTNULL,PRIMARYKEY(id))

Third, we create aBEFORE UPDATEtrigger to be invoked before a change is made to theemployeestable.DELIMITER$$CREATETRIGGERbefore_employee_updateBEFOREUPDATEONemployeesFOREACHROWBEGININSERTINTOemployees_auditSETaction='update',employeeNumber=OLD.employeeNumber,lastname=OLD.lastname,changedon=NOW();END$$DELIMITER;

If you take a look at the schema, you will seebefore_employee_updatetrigger under theemployeestable as follows:

Now it’s time to update an employee record to test if the trigger is really invoked.UPDATEemployeesSETlastName='Phan'WHEREemployeeNumber=1056

To check if the trigger was invoked by theUPDATEstatement, we can query theemployees_audittable by using the following query:SELECT*FROMemployees_audit

The following is the output of the query:

As you see, our trigger was really invoked so that we have a new record in theemployees_audittable.

In this tutorial, you have learned how to create a trigger in MySQL. We also shown you how to develop a trigger to audit the changes of theemployeestable.

Related TutorialsMySQL Triggers Implementation

Working with MySQL Scheduled Event

原文链接：http://outofmemory.cn/mysql/trigger/create-the-first-trigger-in-mysql