Triggers in MySQL

Trigger

Scene:

Log system, record what to do with student tables!

The problem solved:

1, to get each student record is modified the time, in order to issue the record log action!

2, to perform a certain operation, you need to get the current record of processing information!

Trigger: A programming design! JS-like program design based on event programming concept! You can set an event on each record in a table to listen to some of the actions on that table! Once the behavior of the listener is present, the corresponding code is executed.

Record =button

(Modify, delete, add) =click

Perform Operation =alert (' Hello ');

All of the above behaviors are done using sql:

Grammar

Create TRIGGER Trigger name trigger condition, listen to the content, trigger the action after execution

CREATE TRIGGER trigger_name trigger_time trigger_event on tbl_name for each ROW trigger_stmt

Where the trigger condition, event. is made up of the timing of the event, and the content of the event

Timing: Before before, and after after!

Content: Add Insert, remove delete, modify update

Therefore, there are only six types of events:

Before insert before delete before update

After insert after delete after update

The subject of the listener is emitted by the records in the table.

On table_name for each row

The operation that is performed is a collection of SQL!

Create trigger Test_trigger After insert--event on select_student to each row--which table's record is listening insert into student_log values (n ull, ' Insert ', now (), ' New ID ')--executes the SQL collection;

Create a log table

CREATE TABLE Student_log (ID int primary key auto_increment,op varchar (), op_time datetime,ps varchar (255));

　　

To perform an insert:

INSERT into select_student values (null, ' Ouyang Fung ', ' Male ', 22, 1234.56, 178.00);

　　

Delete Trigger

Drop TRIGGER Trigger Name

Get the current triggered record information in the trigger program

There are, two of them!

New (newer), old (older)

New, and old, all indicate the record of the triggering program!

NEW: a fresh record. Old: Older Records!

Depends on the current operation (Intser,update,delete) to use one of them:

Insert, add record, no old record, only new keyword can be used

Delete, deleted record, no new record, only old available!

Update, updates, both new and old records, before the update is a new record, and after the update is a record! So you can have new and old

Record, the current student is deleted, the log is logged, and the student's ID is required to be recorded.

Create trigger Log_del_stu after Deleteon select_student for each rowinsert to Student_log values (null, ' delete ', now () , Old.id)--Executes the SQL collection;

　　

testing, deleting records:

Create trigger Log_upd_stu after Updateon select_student for each row to determine if the student's height is greater than 175 log;

　　

At this point, pay attention to the trigger, with the specific syntax of the execution time:

When: INSERT INTO table operation!

Determine if there is a before insert trigger! There is a trigger program to execute!

Really do insert into

Determine if there are after insert triggers! There is a trigger program to execute!

Update log:

Record the update log, the request is, only a certain part of the students to complete the update log!

Record only, taller than 175 student's update record! Record student ID and height before modification

Additional conditional judgment is required!

Logical branching statements:

If condition Then

Statement body

else if condition then

Statement body

....

Else

Statement body

End If;

Terminator problems with SQL statements

You can modify the outermost statement terminator to achieve the goal!

Delimiter $$

Modify statement end to $$

Remember to change it back when you're done with it!

Delimiter $ $create Trigger Log_upd_stu after Updateon select_student for each rowbeginif old.height > 175 Theninsert in to Student_log values (null, ' Update ', now (), concat (Old.id, ': ', old.height, '---', new.height)); end if;end$ $delimiter;

If the trigger is composed of multiple statements, block. At this point, you need to use

Begin

End wraps the statement block!

Note, about triggers:

1, an event on a table can only have one trigger, if necessary, only the original removed, added!

2, as long as the event occurs, the triggering program may execute! A single statement can trigger multiple triggers!

For example:

INSERT into on duplicate key update

Before insert trigger, insert operation failed before update trigger, update operation, after update

Before insert trigger, insert operation succeeded after insert trigger