What is the syntax of triggers in Mysql

1. Definition:

A program embedded in MySQL includes triggers and stored procedures, both of which have similarities. A trigger is an action triggered by an event. When the database executes these events, triggers are activated to perform the corresponding actions. In MySQL, trigger conditions include UPDATE, INSERT and DELETE operations.

• In MySQL, currently only UPDATE, INSERT, and DELETE will trigger triggers, and other operations do not support triggers. This is very different from triggers in Oracle, so pay attention to the distinction.

• Only one trigger is allowed for each event in each table of triggers. Therefore, a maximum of 6 triggers are allowed per table value. If a table needs to trigger two events, INSERT and DELETE, you need to create two triggers respectively.

• Only tables support triggers, views and temporary tables do not.

2. Syntax:

CREATE TRIGGER TRIGGER_NAME
BEFORE/AFTER 
UPDATE/INSERT/DELETE ON TABLE_NAME 
FOR EACH ROW
#触发器主体
[BEGIN]
SQL操作
[END];

• Creating a trigger: It is the same as creating a stored procedure, using the CREATE statement, but be careful , there is no OR REPLACE here.

• TRIGGER_NAME: Trigger name. In MySQL, it is necessary to ensure that the triggers in each table are unique, and the triggers in each database may not be unique. This means that two tables in a database can have the same triggers. This behavior is not allowed in other databases. To maintain formatting consistency, TRIGGER_NAME should be as unique as possible.

• BEFORE/AFTER: The moment when the trigger is fired. If you want the trigger to execute before the event fires, you can use "BEFORE". If you want the trigger to execute after the event fires, you can use "AFTER".

• Operation: UPDATE/INSERT/DELETE will trigger corresponding events and conditions. MySQL only allows these three DDL operations to support triggers, and other operations are not supported.

• FOR EACH ROW: Each row affected by the trigger event must activate the trigger action. In MySQL, row triggering means that FOE EACH ROW cannot be omitted and statement triggering is not supported.

• Trigger body: It can be either a single SQL statement or a complex structural block composed of BEGIN...END.

# 创建一个触发器T1，在对表customer做插入操作时，就会触发T1，之后会在日志表note中插入一条数据
CREATE TRIGGER T1
AFTER 
INSERT ON customer
FOR EACH ROW # 触发器主体
INSERT INTO note(日期,目标,操作) VALUES(NOW(),'customer','insert');

3. Delete triggers

Similar to deleting tables and stored procedures, the DROP statement is used to delete triggers.

#删除触发器T1
DROP TRIGGER IF EXISTS T1;

Triggers cannot be updated and modified. If you want to modify the content of a trigger, you can only delete the trigger first and then create a new trigger.

4. Query trigger

#在已知数据库内，查询触发器
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_NAME = '触发器名'
#查询所有的触发器
SELECT * FROM information_schema.TRIGGERS（WHERE TRIGGER_SCHEMA = '数据库名'）

5. Use of trigger types OLD and NEW

Triggers are triggered before or after DDL operations, and will always be done to the target table. When certain changes are made, sometimes we need to know the values ​​before and after the target table is changed, which requires the usage of OLD and NEW.

As the name suggests, OLD is the value before the change, which is the value before UPDATE or that is to be DELETE or has been DELETE.

And NEW is the new value after UPDATE, which is the value that is about to be INSERTed or has been INSERTed.

Specific example:

#创建customer表
CREATE TABLE `customer` 
(`CUST_ID` int(11) NOT NULL AUTO_INCREMENT,
`CUST_NAME` varchar(10) NOT NULL,
`CUST_TEL` varchar(10) DEFAULT NULL,
PRIMARY KEY (`CUST_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

#向表中插入数据

#创建一个update触发器
CREATE TRIGGER T2
AFTER 
UPDATE ON customer
FOR EACH ROW 
#old和new的使用方法：old.columnname/new.columnname（列名）
#将更新前后的值，赋值给两个变量
SELECT OLD.CUST_ID,NEW.CUST_ID INTO @OLD_ID,@NEW_ID;

#触发触发器T2
UPDATE customer SET CUST_ID = '10000' WHERE CUST_NAME = 'AAA';

#查询OLD和NEW
SELECT @OLD_ID,@NEW_ID;

The above is the detailed content of What is the syntax of triggers in Mysql. For more information, please follow other related articles on the PHP Chinese website!