Simple MySQL trigger instance

Source: Internet
Author: User

1. Syntax:
Create trigger <TRIGGER Name> -- the TRIGGER must have a name of up to 64 characters, which may be followed by a separator. It is similar to the naming method of other objects in MySQL.
{BEFORE | AFTER} -- trigger has the execution time setting: it can be set to BEFORE or AFTER an event occurs.
{INSERT | UPDATE | DELETE} -- trigger events can also be set: they can be triggered during insert, update, or delete execution.
ON <Table Name> -- a trigger belongs to a table. When an insert, update, or delete operation is performed ON the table, the trigger is activated. we cannot schedule two triggers for the same event of the same table.
For each row -- trigger execution interval: The for each row clause notifies the trigger to execute an action every ROW, instead of executing an action FOR the entire table.
<Trigger SQL statement> -- the trigger contains the SQL statement to be triggered: The statement here can be any legal statement, including compound statements, but the statements here are subject to the same restrictions as those of functions.

-- You must have considerable permissions to CREATE a TRIGGER. If you are a Root user, this is enough. This is different from the SQL standard.

2. Simple Example:
Example1:
-- Create Table tab1
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 a trigger: t_afterinsert_on_tab1
-- Function: automatically adds a record to the tab2 table after adding a record to the tab1 table.
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
Insert into tab1 (tab1_id) values ('20140901 ');

-- Check the result
SELECT * FROM tab1;
SELECT * FROM tab2;

Example2:
-- Create a trigger: t_afterdelete_on_tab1
-- Purpose: Delete the records in Table tab1 and automatically delete the records in Table tab2.
Drop trigger if exists t_afterdelete_on_tab1;
Create trigger t_afterdelete_on_tab1
After delete on tab1
FOR EACH ROW
BEGIN
Delete from tab2 where tab2_id = old. tab1_id;
END;

-- Test
Delete from tab1 WHERE tab1_id = '20140901 ';
-- Check the result
SELECT * FROM tab1;
SELECT * FROM tab2;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.