触发器是一个特殊的存储过程,不同的是,存储过程要用call来调用,触发器在一个特定的事件发生的时候,mysql自动调用触发器。
创建语法
CREATE TRIGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trirgger_stmt
其中,trigger_name 是触发器的名称;trigger 是触发的时机,可以指定为before,after;trigger_event是触发事件,包括UPDATE ,DELETE 等;tb_name是创建触发器的表名;triggera_subtmt是触发器的程序体。
例如:
CREATE TABLE account(acc_num INT,amount DECIMAL);
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum=@sum+NEW.amount;
set @sum=0;
INSERT INTO account VALUES(3,3.00),(4,4.00);
mysql> SELECT @sum;
+------+
| @sum |
+------+
| 7 |
+------+
创建多个执行语句的触发器
CREATE TRIGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trirgger_stmt
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);
创建触发器
mysql> DELIMITER //
mysql> 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),(NULL),
(NULL),(NULL),(NULL),(NULL),(NULL);
INSERT INTO test4 (a4) values
(0),(0),(0),(0),(0),
(0),(0),(0),(0),(0);
INSERT INTO test1 VALUES (1),(3),(1),(7),(8),(4),(4);
查看触发器执行情况
查看触发器
SHOW TRIGERS \G
在information_schema数据库中中查看触发器信息
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS WHERE condition;
例如:
SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
WHERE TRIGGER_NAME='testref'\G
删除触发器
DROP TRIGGER [SCHEMA.] trigger_name
例如:
DROP TRIGGER testref;