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

MySQL triggers, like stored procedures, are a piece of program embedded in MySQL. A trigger is an action triggered by time, including INSERT, UODATE, and DELETE statements. If a trigger is defined, the corresponding action is triggered when the database executes these statements. The trigger is a named database object related to the table, which is activated when a specific event occurs on the table.

11.1. Create a trigger

Trigger is a special stored procedure, the difference is that the execution of the stored procedure is called by the call statement, and the execution of the trigger does not need to be called by the car statement, nor does it need to be started manually, as long as the predefined time occurs, it will be called automatically by MySQL.

The syntax for creating a trigger is:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

Trigger_name: trigger name, specified by the user

Trigger_time: trigger time, which can be specified as before or after

Trigger_event: identifies the trigger event

Tbl_name: the table name that identifies the trigger

Trigger_body: trigger execution statement

Create a trigger for a single execution statement:

Mysql > CREATE TABLE account (acc_num INT,amount DECIMAL); Query OK, 0 rows affected (0.06 sec) mysql > CREATE TRIGGER ins_sum BEFORE INSERT ON account-> FOR EACH ROW SET @ sum= @ sum + NEW.amount;Query OK, 0 rows affected (0.02 sec) mysql > SET @ sum=0;Query OK, 0 rows affected (0.00 sec) mysql > INSERT INTO account VALUES (1Magazine 1.00), (2Magol 2.00); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT @ sum +-+ | @ sum | +-+ | 3.00 | +-+ 1 row in set (0.00 sec)

Syntax for creating multiple triggers that execute statements:

CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW BEGIN trigger_stmt END

11.2. View triggers

Viewing triggers means to view the definition, status and trigger information of triggers that already exist in the database. You can view trigger information through SHOW TRUGGERS and in the triggers table.

Mysql > CREATE TABLE myevent-> (- > id int (11) DEFAULT NULL,-> evt_name char (20) DEFAULT NULL->); Query OK, 0 rows affected (0.03 sec) mysql > CREATE TRIGGER trig_update AFTER UPDATE ON account-> FOR EACH ROW INSERT INTO myevent VALUES Query OK 0 rows affected (0.05sec) mysql > SHOW TRIGGERS\ gateway * 1. Row * * Trigger: ins_sum Event: INSERT Table: account Statement: SET @ sum = @ sum + NEW.amount Timing: BEFORE Created: NULL sql_mode: Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci** 2.row * * Trigger: trig_update Event: UPDATE Table: account Statement: INSERT INTO myevent VALUES (1 'AFTER UPDATE') Timing: AFTER Created: NULL sql_mode: Definer: root@localhostcharacter_set_client: utf8collation_connection: utf8_general_ci Database Collation: utf8_general_ci2 rows in set (0.00 sec)

The definitions of all triggers in MySQL are stored in the TRIGGERS table of the INFORMATION_SCHEMA database and can be viewed through SELECT. The syntax format is as follows:

SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition Mysql > SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE TRIGGER_NAME='trig_update'\ gateway * 1. Row * * TRIGGER_CATALOG: def TRIGGER_SCHEMA: test TRIGGER_NAME: trig_update EVENT_MANIPULATION: UPDATE EVENT_OBJECT_CATALOG: def EVENT_OBJECT_SCHEMA: test EVENT_OBJECT_TABLE: account ACTION_ORDER: 0 ACTION_CONDITION: NULL ACTION_STATEMENT: INSERT INTO myevent VALUES (1 'AFTER UPDATE') ACTION_ORIENTATION: ROW ACTION_TIMING: AFTERACTION_REFERENCE_OLD_TABLE: NULLACTION_REFERENCE_NEW_TABLE: NULLACTION_REFERENCE_ OLD_ROW: OLD ACTION_REFERENCE_NEW_ROW: NEW CREATED: NULL SQL_MODE: DEFINER: root@localhost CHARACTER_SET_CLIENT: utf8 COLLATION_CONNECTION: utf8_general_ci DATABASE_COLLATION: utf8_general_ci1 row in set (0.00 sec)

11.3. Use of triggers

A trigger is a named database object associated with a table that is activated when a specific time appears on the table.

Create a trigger to update the myevent data table after the insert table inserts the data.

Mysql > CREATE TRIGGER trig_inster AFTER INSERT ON account-> FOR EACH ROW INSERT INTO myevent VALUES; Query OK, 0 rows affected (0.02 sec) mysql > INSERT INTO account VALUES; Query OK, 2 rows affected (0.01 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > SELECT * FROM myevent +-+-+ | id | evt_name | +-+-+ | 2 | AFTER INSERT | | 2 | AFTER INSERT | +-+-+ 2 rows in set (0.00 sec)

11.4. Delete trigger

Use the DROP TRIGGER statement to delete triggers that already exist in MySQL in the syntax format:

DROP TRIGGER [schema_name.] trigger_name

Delete a trigger

Mysql > DROP TRIGGER test.ins_sum;Query OK, 0 rows affected (0.02 sec)

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.