软件测试 | 创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event  ON tbl_name FOR EACH ROW trigger_stmt

DELIMITER $$ CREATE TRIGGER ins_film AFTER INSERT ON film FOR EACH ROW BEGIN  INSERT INTO film_text (film_id, title, description)  VALUES (new.film_id, new.title, new.description); END; $$ delimiter ;

mysql> INSERT INTO film VALUES  -> (1001,'ACADEMY DINOSAUR',  -> 'A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies',  -> 2006,1,NULL,6,'0.99',86,'20.99','PG','Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42'); Query OK, 1 row affected (0.05 sec) mysql> select * from film_text where film_id=1001 \G *************************** 1. row ***************************  film_id: 1001  title: ACADEMY DINOSAUR description: A Epic Drama of a Feminist And a Mad Scientist who must Battle a Teacher in The Canadian Rockies 1 row in set (0.00 sec)

--创建 BEFROE INSERT、AFTER INSERT、BEFORE UPDATE、AFTER UPDATE 触发器： mysql> create table tri_demo(id int AUTO_INCREMENT,note varchar(20),PRIMARY KEY (id)); Query OK, 0 rows affected (0.03 sec) mysql> CREATE TRIGGER ins_film_bef  -> BEFORE INSERT ON film FOR EACH ROW BEGIN  -> INSERT INTO tri_demo (note) VALUES ('before insert');  -> END;  -> $$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE TRIGGER ins_film_aft  -> AFTER INSERT ON film FOR EACH ROW BEGIN  -> INSERT INTO tri_demo (note) VALUES ('after insert');  -> END;  -> $$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE TRIGGER upd_film_bef  -> BEFORE update ON film FOR EACH ROW BEGIN  -> INSERT INTO tri_demo (note) VALUES ('before update');  -> END;  -> $$ Query OK, 0 rows affected (0.00 sec) mysql> CREATE TRIGGER upd_film_aft  -> AFTER update ON film FOR EACH ROW BEGIN  -> INSERT INTO tri_demo (note) VALUES ('after update');  -> END;  -> $$ Query OK, 0 rows affected (0.00 sec) --插入记录已经存在的情况： mysql> select film_id,title from film where film_id = 1001; +---------+------------------+ | film_id | title | +---------+------------------+ | 1001 | ACADEMY DINOSAUR | +---------+------------------+ 1 row in set (0.00 sec) mysql> INSERT INTO film VALUES  -> (1001,'Only test', -> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',  -> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')  -> ON DUPLICATE KEY  -> UPDATE title='update record'; Query OK, 2 rows affected (0.05 sec) mysql> select * from tri_demo; +----+---------------+ | id | note | +----+---------------+ | 1 | before insert | | 2 | before update | | 3 | after update | +----+---------------+ 3 rows in set (0.00 sec) --插入新记录的情况： mysql> delete from tri_demo; Query OK, 3 rows affected (0.00 sec) mysql> select film_id,title from film where film_id = 1002; Empty set (0.00 sec) mysql> INSERT INTO film VALUES  -> (1002,'Only test',  -> 'Only test',2006,1,NULL,6,'0.99',86,'20.99','PG',  -> 'Deleted Scenes,Behind the Scenes','2006-02-15 05:03:42')  -> ON DUPLICATE KEY  -> UPDATE title='update record'; Query OK, 1 row affected (0.05 sec) mysql> mysql> select * from tri_demo; +----+---------------+ | id | note | +----+---------------+ | 4 | before insert | | 5 | after insert | +----+---------------+ 2 rows in set (0.00 sec)