MySQL Trigger instance Tutorial (Create delete Modify view)

1. Create the statement for the trigger:

CREATE TRIGGER < trigger name > <--

{before | After}

{INSERT | UPDATE | DELETE}

On < table name >

For each ROW

< triggers SQL statements >

-Triggers

The code is as follows

Copy Code

--create Trigger name --on Table name --Trigger action --for/after insert/update/delete --as --t-sql Select * from SHOP_JB --insert into Shop_xs values (6,4000,1,getdate (), 3) --Update SHOP_JB set stock=stock-1 where id=6 --Create TRIGGER C Reate trigger Tri1 on Shop5_xs after insert as Update SHOP3_JB set stock=stock-1 where id=6 ---triggers more When you are finished, you want to view the table Create trigger Tri3 on SHOP3_JB for update as Select * from SHOP3_JB Update shop3_jb Set spec= ' sp-z99 ' where id=2 Drop trigger TRI1 ---implemented using stored procedures, and after the update, to view the table Alter proc PROC1 as Update SHOP3_JB set spec= ' sp-111 ' where id=2 Select * from SHOP3_JB Exec proc1 ---View table triggers exec Sp_helptrig GER table name ---View the contents of a table exec sp_helptext trigger name ---Delete trigger Drop trigger trigger name

Tip: You must have considerable permissions to create triggers (create TRIGGER), if you are already root, then that's enough. This is different from the SQL standard.

Instance:

Example1:

CREATE TABLE Tab1:

The code is as follows	Copy Code
DROP TABLE IF EXISTS tab1; CREATE TABLE Tab1 ( tab1_id varchar (11) ); CREATE TABLE TaB2: DROP TABLE IF EXISTS tab2; CREATE TABLE TAB2 ( tab2_id varchar (11) );

Create Trigger: T_AFTERINSERT_ON_TAB1

Effect: Increase the TAB1 table record and automatically add the record to the TAB2 table

The code is as follows	Copy Code
DROP TRIGGER IF EXISTS t_afterinsert_on_tab1; CREATE TRIGGER T_AFTERINSERT_ON_TAB1 After INSERT on TAB1 For each ROW BEGIN Insert into TAB2 (tab2_id) values (new.tab1_id); End;

Test:

The code is as follows	Copy Code
INSERT into TAB1 (tab1_id) VALUES (' 0001 ');

Look at the results. Estimated two tables have the same data!

----------Practice--------

The code is as follows

Copy Code

Create Trigger Tri1 On SHOP3_JB After insert As SELECT * from inserted INSERT into SHOP3_JB values (' Washing machine ', ' AA ', 200,500,getdate ()) SELECT * FROM SHOP3_JB EXEC sp_helptrigger SHOP3_JB EXEC sp_helptext Tri1 Drop Trigger Tri1 Create Trigger Tri2 On SHOP3_JB After delete As SELECT * from deleted Delete from SHOP3_JB where namel= ' notebooks ' SELECT * FROM SHOP3_JB ---a trigger to view a table EXEC sp_helptrigger table name ---View the contents of a table EXEC sp_helptext Trigger Name ---delete triggers Drop TRIGGER Trigger Name SELECT * FROM Shop5_xs SELECT * FROM SHOP3_JB

Supplemental syntax

Trigger syntax

3.1 CREATE TRIGGER trigger_name trigger_time trigger_event

On tbl_name for each ROW trigger_stmt
Trigger_time is the action time that triggers the program. It can be either before or aftertrigger_event indicating the type of statement that activates the triggering program. Trigger_event can be one of the following values:

· Insert: Activates a trigger when inserting a new row into a table, for example, through INSERT, LOAD data, and replace statements.
· Update: Activates a trigger when a row is changed, for example, through an UPDATE statement.
· Delete: Activates a trigger when a row is deleted from a table, for example, through the Delete and replace statements.
3.2 Problems that may be encountered
If you insert/update the data you just inserted in the trigger, it will cause a loop call.

Such as:

Create trigger test before update on test for each row update test set new.updatetime = Now () where id=new.id; End

You should use set:

Create trigger test before update on test for each row set new.updatetime = Now (); End

3.3 Triggers and stored procedures

The trigger cannot invoke the stored program that returns the data to the client or use dynamic SQL with the call statement

(Allows the stored program to return data to the trigger by parameter).
and stored procedures can accept parameters, and the results range to the application