Triggers for SQL Programming summary

Compared to the mysql5.1 Handbook, this paper summarizes the SQL programming of the past few days, mainly related to triggers, stored procedures, rights Management, master-slave separation and so on, the right when the expert, please skip.

The flip-flop is to trigger the execution of a specific SQL statement before or after the specified data table is added or deleted, and the data table refers to the persistent table. You cannot associate a trigger with a temporary table or view. The trigger can be understood in four ways:

---monitoring location table
---monitoring event insert/update/delete
---trigger time after/before
---trigger event insert/update/delete

Create syntax:

Create TRIGGER Trigger Name
After/before
Insert/update/delete
On table name
For each row
Begin
SQL1;
..
SQLN;
End

First declaration Terminator

Delimiter $---The default is, the end of the number, but the SQL statement after the end of the semicolon after the execution of the trigger will report a syntax error, so you want to declare the Terminator in advance of the semicolon

---Two tables needed to create the test

CREATE TABLE ' goods ' (

' GID ' int (one) DEFAULT NULL,
' Name ' varchar (DEFAULT NULL),
' Num ' smallint (6) DEFAULT NULL
) Engine=innodb DEFAULT charset=utf8$

CREATE TABLE ' orders ' (

' OID ' int (one) DEFAULT NULL,
' GID ' int (one) DEFAULT NULL,
' Much ' smallint (6) DEFAULT NULL
) Engine=innodb DEFAULT charset=utf8$
Inserting test data

INSERT into goods VALUES (1, ' BMW ', +), (2, ' mini '), (3, ' Ford ', 50) $

---Create a trigger T2, watch the order form, and when the order table increases the order, the commodity table will have to reduce the corresponding quantity of goods;

Delimiter $
Create TRIGGER T2
After
Insert--does not allow the same table to have two identical actions at the same time
On orders
For each row
Begin
Update goods set Num=num-new.much where GID =new.gid;--New.much is the value of the New Order table row;
end$

---call

INSERT into orders VALUES (123,1,2) $

SELECT * from goods$ again to view data before and after comparison

--Create the trigger T3, monitor the order form, and when the order is deleted, add the corresponding quantity to the commodity table;

Create TRIGGER T3
After
Delete
On orders
For each row
Begin
Update goods set Num=num+old.much where GID =old.gid;
end$

--Create a trigger T5, monitor the order table, determine whether the order is larger than the inventory before it increases, if it is larger than the inventory, let it be equal to the inventory difference before and after

Create TRIGGER T5
Before
Insert
On orders

For each row
Begin

declare rnum int;

Select num into rnum from goods where gid=new.gid;

If New.much>rnum Then
Set New.much =rnum;
End If;

Update goods set Num=num-new.much where GID =new.gid;
end$

---delete a trigger

DROP TRIGGER [schema_name.] trigger_name

Discard the triggering program. The scheme name (schema_name) is optional. If schema is omitted, the trigger is discarded from the current scenario.

is usually the name of the trigger directly deleted.

Triggers for SQL Programming summary