创建一个包含多个执行语句的触发器。
1、首先创建表 test1~test4,插入数据
CREATE TEBLE 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);
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);
2、创建触发器
DELIMITER //
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 ;
3、向test1插入数据测试触发器
INSERT INTO test1 VALUES(1),(3),(1),(7),(1),(8),(4),(4);
4、SELECT 语句查看test1~test4结果