mysql触发器限制删除_13 MySQL触发器

触发器是与表有关的数据库对象,在满足条件时触发,并执行触发器中定义的语句集合.

触发器可以协助应用在数据库端确保数据的完整性.

1.创建触发器

CREATE TRIGGER  trigger_name  trigger_time  trigger_event

ON tbl_name   FOR  EACH  ROW

trigger_stmt

注:触发器只能创建在永久表(Permanent Table)上;不能对临时表(Temporary Table)创建触发器.

其中,trigger_time :

BEFORE | AFTER

trigger_event:

INSERT | UPDATE | DELETE

MySQL中,对同一个表相同触发时机的相同触发事件,只能定义一个触发器.如果是不同字段,则通过逻辑判断来处理;而Oracle这种,可以对不同字段的相同事件进行定义不同的触发器.

通过NEW / OLD 来引用触发器中发生变化的记录内容.

目前触发器只支持行级触发器,不支持语句级触发器.

Demo1:

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 ;

对于INSERT INTO .. ON DUPLICATE KEY UPDATE .. 语句来说,触发触发器的顺序可能会造成疑惑.

Demo2:

--创建 BEFORE INSERT , AFTER INSERT , BEFORE UPDATE , AFTER UPDATE 触发器

CREATE TABLE tri_demo (

id INT AUTO_INCREMENT

,note VARCHAR(20)

,PRIMARY KEY(id)

);

DELIMITER $$

CREATE TRIGGER ins_film_bf

BEFORE INSERT ON film FOR EACH ROW

BEGIN

INSERT INTO tri_demo (node)

VALUES('before insert');

END ;

$$

CREATE TRIGGER ins_film_af

AFTER INSERT ON film FOR EACH ROW

BEGIN

INSERT INTO tri_demo (note)

VALUES('after insert');

END ;

$$

CREATE TRIGGER update_film_bf

BEFORE UPDATE ON film FOR EACH ROW

BEGIN

INSERT INTO tri_demo(note)

VALUES('before update');

END ;

$$

CREATE TRIGGER update_film_af

AFTER UPDATE ON film FOR EACH ROW

BEGIN

INSERT INTO tri_demo(note)

VALUES ('after update');

END;

$$

-- 1001记录已存在

INSERT INTO film VALUES

(1001,'Only Test','Only Test',2006,1,NULL,6,'0.99',86,'20.99','PG','xxx','2006-02015 05:03:43')

ON DUPLICATE KEY

UPDATE title='update record';

-- 1002记录不存在

INSERT INTO film VALUES

(1002,'Only Test','Only Test',2006,1,NULL,6,'0.99',86,'20.99','PG','xxx','2006-02015 05:03:43')

ON DUPLICATE KEY

UPDATE title='update record';

结论:对于ON DUPLICATE KEY , 如果已经有过数据,则触发顺序为 BEFORE INSERT > BEFORE UPDATE > AFTER UPDATE (首先准备insert,而后发现已有数据,准备update,完成update);如果是新数据插入,则触发顺序就是 BEFORE INSERT > AFTER INSERT .

2.删除触发器

DROP TRIGGER [schema_name.]trigger_name

3.查看触发器

1).使用SHOW TRIGGERS 命令.

此方式不能查询指定触发器的信息,不方便.

2).查看系统表information_schema.triggers

可以查询指定触发器的信息.

DESC triggers ;

SELECT * FROM triggers WHERE trigger_name = 'xxx' ;

4.触发器的使用

1).触发器的限制

a.可以通过存储过程/函数的 OUT/INOUT 类型参数,返回给触发器.但是不可以调用直接返回数据的过程.不能采用 CALL 语句的动态SQL 语句.

2).MySQL的触发器,是按照 BEFORE触发器,行操作,AFTER触发器顺序执行.

如果有一步发生错误,都不会执行剩下的操作.

如果是针对事务表进行的操作,那么整个作为一个事务的回滚;

如果是针对非事务表, 那么已经更新的记录将无法回滚,需要注意.