Shulou(Shulou.com)06/01 Report--

This paper gives an example to describe the definition and usage of MySQL trigger. Share with you for your reference, the details are as follows:

Grammar

CREATE TRIGGER trigger name-the trigger must have a name, up to 64 characters, and may be followed by a delimiter. It is basically similar to the naming of other objects in MySQL.

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

{INSERT | UPDATE | DELETE}-you can also set triggered events: they can be triggered during the execution of insert, update, or delete.

ON table name-the trigger belongs to a table: when an insert, update, or delete operation is performed on this table, the trigger is activated. We cannot schedule two triggers for the same event in the same table.

FOR EACH ROW-the interval at which the trigger executes: the FOR EACH ROW clause tells the trigger to perform an action every other row, rather than on the entire table.

-- the trigger contains the SQL statement to be triggered: the statement here can be any legal statement, including the compound statement, but the statement here is subject to the same restrictions as the function.

Example preparation work

-create table tab1DROP TABLE IF EXISTS tab1;CREATE TABLE tab1 (tab1_id varchar (11));-create table tab2DROP TABLE IF EXISTS tab2;CREATE TABLE tab2 (tab2_id varchar (11))

Example 1: add a new entry to trigger the addition of another table

-- create trigger: add records to the tab1 table automatically after adding records to the tab2 table; create TRIGGER t_afterinsert_on_tab1AFTER INSERT ON tab1FOR EACH ROWBEGIN insert into tab2 (tab2_id) values (new.tab1_id); END;-- test INSERT INTO tab1 (tab1_id) tab1 ('0001');-- look at the result SELECT * FROM tab1;SELECT * tab1

Example 2: delete one entry and trigger the deletion of another table

-- create trigger: delete delete tab1 color-function: delete the corresponding record in the tab1 table automatically after deleting the record in the tab1 table. Delete the corresponding record in the tab2 table automatically. Test the DELETE FROM tab1 WHERE tab1_id='0001';-- to see the result SELECT * FROM tab1;SELECT * FROM tab2.

Example 3: update one to trigger the update of another table

-- create trigger: modify the tab1 table record and update the corresponding record in the tab2 table automatically after modifying the tab2 table record. Test the update tab1 set tab1_id='0002' WHERE tab1_id='0001';-- to see the result SELECT * FROM tab1;SELECT * tab1 create TRIGGER t_afterupdate_on_tab1AFTER UPDATE ON tab1FOR EACH ROWBEGIN update tab2 set tab2_id=new.tab1_id where tab2_id=old.tab1_id;END;--.

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection", "MySQL Database Lock related skills Summary" and "MySQL Common function Summary".

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.