【MySQL】MySQL触发器应用场景和使用方法

小破孩
2022-06-21 / 1 评论 / 371 阅读 / 正在检测是否收录...

触发器是一种特殊的存储过程,它在插入,删除或改动特定表中的数据时触发运行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。

数据库触发器有下面的作用:

1.安全性。能够基于数据库的值使用户具有操作数据库的某种权利。

# 能够基于时间限制用户的操作,比如不同意下班后和节假日改动数据库数据。

# 能够基于数据库中的数据限制用户的操作,比如不同意股票的价格的升幅一次超过10%。

2.审计。能够跟踪用户对数据库的操作。

# 审计用户操作数据库的语句。

# 把用户对数据库的更新写入审计表。

3.实现复杂的数据完整性规则

# 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器能够引用列或数据库对象。比如,触发器可回退不论什么企图吃进超过自己保证金的期货。

# 提供可变的缺省值。

4.实现复杂的非标准的数据库相关完整性规则。触发器能够对数据库中相关的表进行连环更新。比如,在auths表author_code列上的删除触发器可导致对应删除在其他表中的与之匹配的行。

# 在改动或删除时级联改动或删除其他表中的与之匹配的行。

# 在改动或删除时把其他表中的与之匹配的行设成NULL值。

# 在改动或删除时把其他表中的与之匹配的行级联设成缺省值。

# 触发器可以拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这样的触发器会起作用。比如,可以在books.author_code 列上生成一个插入触发器,假设新值与auths.author_code列中的某值不匹配时,插入被回退。

5.同步实时地复制表中的数据。

6.自己主动计算数据值,假设数据的值达到了一定的要求,则进行特定的处理。比如,假设公司的帐号上的资金低于5万元则马上给財务人员发送警告数据。

mysql创建触发器

1、老版本mysql语法:

CREATE TRIGGER <触发器名称> --触发器必须有名字,最多64个字符,可能后面会附有分隔符.它和MySQL中其他对象的命名方式基本相象.

{ BEFORE | AFTER } --触发器有执行的时间设置:可以设置为事件发生前或后。

{ INSERT | UPDATE | DELETE } --同样也能设定触发的事件:它们可以在执行insert、update或delete的过程中触发。

ON <表名称> --触发器是属于某一个表的:当在这个表上执行插入、 更新或删除操作的时候就导致触发器的激活. 我们不能给同一张表的同一个事件安排两个触发器。

FOR EACH ROW --触发器的执行间隔:FOR EACH ROW子句通知触发器 每隔一行执行一次动作,而不是对整个表执行一次。

<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的语句可以是任何合法的语句, 包括复合语句,但是这里的语句受的限制和函数的一样。

2、新版本mysql语法

    CREATE
        [DEFINER = { user | CURRENT_USER }]
        TRIGGER trigger_name
        trigger_time trigger_event
        ON tbl_name FOR EACH ROW
        [trigger_order]
        trigger_body
    trigger_time: { BEFORE | AFTER }
    trigger_event: { INSERT | UPDATE | DELETE }
    trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
注意:或许你必须拥有相当大的权限才能创建触发器(CREATE TRIGGER),如果你已经是Root用户,那么就足够了。这跟SQL的标准有所不同。
mysql5.7新版本语法发生了一些小小的变化,同时也可以自定义用户,语法相对来说简单了很多,原理和老版本还是一样的;

准备工作:

1、创建表tab1

sql命令:

    DROP TABLE IF EXISTS tab1;
    CREATE TABLE tab1(
        tab1_id varchar(11)
    );

2、创建表tab2

sql命令:

    DROP TABLE IF EXISTS tab2;
    CREATE TABLE tab2(
        tab2_id varchar(11)
    );

实例1:
1、创建触发器:t_afterinsert_on_tab1

作用:增加tab1表记录后自动将记录增加到tab2表中
老版本sql命令:
    DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
    CREATE TRIGGER t_afterinsert_on_tab1
    AFTER INSERT ON tab1
    FOR EACH ROW
    BEGIN
    insert into tab2(tab2_id) values(new.tab1_id);
    END;
新版本sql命令:
    DROP TRIGGER IF EXISTS t_afterinsert_on_tab1;
    CREATE
       TRIGGER t_afterinsert_on_tab1
       AFTER INSERT
       ON tab1 FOR EACH ROW
       insert into tab2(tab2_id) values(new.tab1_id);

2、向tab1表插入一条数据做测试;

sql命令:

INSERT INTO tab1(tab1_id) values('0001');

3、查看tab1表和tab2表的结果;

sql命令:
    SELECT * FROM tab1;
    SELECT * FROM tab2;

实例2:
1、创建触发器:t_afterdelete_on_tab1

作用:删除tab1表记录后自动将tab2表中对应的记录删去
老版本sql命令:
    DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
    CREATE TRIGGER t_afterdelete_on_tab1
    AFTER DELETE ON tab1
    FOR EACH ROW
    BEGIN
    delete from tab2 where tab2_id=old.tab1_id;
    END;
新版本sql命令:
    DROP TRIGGER IF EXISTS t_afterdelete_on_tab1;
    CREATE
    TRIGGER t_afterdelete_on_tab1
    AFTER DELETE
    ON tab1 FOR EACH ROW
    delete from tab2 where tab2_id=old.tab1_id;

2、删除tab1表一条数据

sql命令:

 `DELETE FROM tab1 WHERE tab1_id='0001';` 


3、查看tab1表和tab2表的结果

sql命令:
    SELECT * FROM tab1;
    SELECT * FROM tab2;

三、mysql查看触发器

和查看数据库(show databases;)查看表格(show tables;)一样,查看触发器的语法如下:
 `SHOW TRIGGERS [FROM schema_name];` 
其中,schema_name 即 Schema 的名称,在 MySQL 中 Schema 和 Database 是一样的,也就是说,可以指定数据库名,这样就不必先“USE database_name;”了。


实例:
1、查看tab1表下面所有的触发器:

sql命令:

 `SHOW TRIGGERS` 

2、查看test数据库下面的触发器,tabl表属于test数据库:

sql命令:

 `SHOW TRIGGERS FROM test` 

四、mysql删除触发器

和删除数据库、删除表格一样,删除触发器的语法如下:

 `DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name` 


实例:
1、删除触发器‘t_afterdelete_on_tab1’

sql命令:

 `DROP TRIGGER IF EXISTS t_afterdelete_on_tab1` 

参考:付杰博客 https://www.fujieace.com/mysql/trigger.html

1

评论 (1)

取消
  1. 头像
    青山
    Windows 10 · Google Chrome

    写得很详细

    回复