Database basics & lt; 4 & gt; triggers

1. Concepts

Trigger is a special event-driven process defined by the user in the relational table. Once defined

The server automatically activates corresponding triggers for table addition, deletion, and modification, and implements centralized Integrity Control at the DBMS core layer.

2. Define a trigger

Create trigger <trigger Name> before | after <trigger event> on <Table Name>

For each row | statement [when <trigger condition>] <trigger action body>

 

Trigger event: it can be an insert, delete, or update event. It can be a combination of several events and is connected with or.

The <trigger column,...>

Trigger types: for each row (row-Level Trigger) and for each statement (statement-Level Trigger, triggered once)

Trigger action body: it can be a process block or a call to a created stored procedure. If it is a row-level trigger, you can

Use new and old to reference the new value after the update/insert event and the old value before the update/delete event

3. Activate a trigger

Execution sequence of multiple triggers on the same table during activation:

1) execute the before trigger on the table.

2) activate the SQL statement of the trigger

3) execute the after trigger on the table.

Check whether the trigger is activated: show triggers

 

4. delete a trigger

Drop trigger <trigger Name> on <Table Name>

5. mysql trigger instance

Create table test1 (a1 INT); create table test2 (a2 INT); create table test3 (a3 int not null AUTO_INCREMENT primary key ); create table test4 (a4 int not null AUTO_INCREMENT primary key, b4 int default 0 ); DELIMITER |/* change the input Terminator */create trigger testref before insert on test1 for each row begin insert into test2 SET a2 = NEW. a1; delete from test3 WHERE a3 = NEW. a1; UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW. a1; END | DELIMITER; insert into test3 (a3) VALUES (NULL), (NULL ), (NULL), (NULL); insert into test4 (a4) VALUES (0), (0), (0), (0 ), (0), (0), (0), (0), (0), (0); If you insert the following values into Table test1, as shown below: mysql> insert into test1 VALUES-> (1), (3), (1), (7), (1), (8), (4), (4 ); query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 then the data in the four tables is as follows: mysql> SELECT * FROM test1; + ------ + | a1 | + ------ + | 1 | 3 | 1 | 7 | 1 | 8 | 4 | 4 | + ------ + 8 rows in set (0.00 sec) mysql> SELECT * FROM test2; + ------ + | a2 | + ------ + | 1 | 3 | 1 | 7 | 1 | 8 | 4 | 4 | + ------ + 8 rows in set (0.00 sec) mysql> SELECT * FROM test3; + ---- + | a3 | + ---- + | 2 | 5 | 6 | 9 | 10 | + ---- + 5 rows in set (0.00 sec) mysql> SELECT * FROM test4; + ---- + ------ + | a4 | b4 | + ---- + ------ + | 1 | 3 | 2 | 0 | 3 | 1 | 4 | 2 | 5 | 0 | | 6 | 0 | 7 | 1 | 8 | 1 | 9 | 0 | 10 | 0 | + ---- + ------ + 10 rows in set (0.00 sec)