MySQL Trigger Trigger

Trigger (Trigger): Monitors a situation and triggers an action, the trigger must have a name, a maximum of 64 characters, and may be appended with a delimiter.

It's basically like naming other objects in MySQL.

Trigger creation Syntax four elements: 1. Monitoring location (table) 2. Monitoring event DML 3. Trigger Time (After/before) 4. Triggering event DML

{before | After}--the trigger has a time setting for execution: it can be set either before or after the event occurs.

{INSERT | UPDATE | DELETE}-can also set triggered events: they can be triggered during an insert, update, or delete

For each row-the execution interval of the trigger: the For each row clause notifies the trigger to perform an action every other row, rather than performing the entire table once.

# # #执行触发器建议使用ROOT的权限

# # # #使用别名old和new to refer to the columns in the table related to the triggering program. Old.col_name refers to 1 columns in an existing row before updating or deleting it. New.col_name refers to the 1 column of the new row that will be inserted or 1 of the existing row after it has been updated

column, so to refer to the column before the update is old, to refer to the updated column with the new

Grammar:

Create Trigger Triggername

After/before Insert/update/delete on TableName

For each row# #固定格式

Begin

SQL statements;

End

# #来个简单操作:

1. Add two tables:

CREATE TABLE TabA (id INT not NULL PRIMARY key,c_name VARCHAR (20));

CREATE TABLE TabB (id INT not NULL PRIMARY key,c_name VARCHAR (20));

2. Create TRIGGER: Insert

T_aferinsert_on_taba

####

DELIMITER $$

CREATE TRIGGER Test.t_aferinsert_on_taba

After INSERT on Taba

For each ROW

BEGIN

INSERT into Tabb (id,c_name) VALUES (new.id,new.c_name);

END;

$$

DELIMITER;

3, view Taba, Tabb table

SELECT a.id as Aid,a.c_name acname,b.id bid,b.c_name bname from Taba a JOIN Tabb b

4. Insert data from the Taba table:

INSERT into Taba (id,c_name) VALUES (1, ' Test triggers ')

SELECT a.id as Aid,a.c_name acname,b.id bid,b.c_name bname from Taba a JOIN Tabb b

650) this.width=650; "Src=" Http://s2.51cto.com/wyfs02/M00/89/9A/wKiom1gYBj2i7PSYAAA8FWqTeDY870.png-wh_500x0-wm_3 -wmp_4-s_3696549830.png "title=" Triggers.png "alt=" Wkiom1gybj2i7psyaaa8fwqtedy870.png-wh_50 "/>

# #UPDATE Simple example:

DELIMITER $$

CREATE TRIGGER T_aferupdate_on_taba

After UPDATE on Taba

For each ROW

BEGIN

UPDATE Tabb SET c_name=new.c_name WHERE id=new.id;

END;

$$

DELIMITER;

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M01/89/99/wKioL1gYDlDDnAKZAABaLjnj1tA386.png-wh_500x0-wm_3 -wmp_4-s_34396873.png "title=" Update.png "alt=" Wkiol1gydlddnakzaabaljnj1ta386.png-wh_50 "/>

# # #DELETE:

DELIMITER $$

CREATE TRIGGER T_aferdelete_on_taba

After DELETE on Taba

For each ROW

BEGIN

DELETE from Tabb WHERE id=old.id;

END;

$$

DELIMITER;

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M02/89/9B/wKiom1gYD4eQWfHtAABSBhd4i10682.png-wh_500x0-wm_3 -wmp_4-s_2004523610.png "title=" Delete.png "alt=" Wkiom1gyd4eqwfhtaabsbhd4i10682.png-wh_50 "/>

This article is from the "DBSpace" blog, so be sure to keep this source http://dbspace.blog.51cto.com/6873717/1868008

MySQL Trigger Trigger