当前位置:Gxlcms > mysql > MYSQL入门学习之十一:触发器的基本操作_MySQL

MYSQL入门学习之十一:触发器的基本操作_MySQL

时间:2021-07-01 10:21:17 帮助过:48人阅读

bitsCN.com

MYSQL入门学习之十一:触发器的基本操作

相关链接:

MYSQL入门学习之一:基本操作

http:///database/201212/173868.html

MYSQL入门学习之二:使用正则表达式搜索

http:///database/201212/173869.html

MYSQL入门学习之三:全文本搜索

http:///database/201212/173873.html

MYSQL入门学习之四:MYSQL的数据类型

http:///database/201212/175536.html

MYSQL入门学习之五:MYSQL的字符集

http:///database/201212/175541.html

MYSQL入门学习之六:MYSQL的运算符

http:///database/201212/175862.html

MYSQL入门学习之七:MYSQL常用函数

http:///database/201212/175864.html

MYSQL入门学习之八:数据库及表的基本操作

http:///database/201212/175867.html

MYSQL入门学习之九:索引的简单操作

http:///database/201212/176772.html

MYSQL入门学习之十:视图的基本操作

http:///database/201212/176775.html

触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句(或位于BEGIN和END语句之间的一组语句):

DELETE;

INSERT;

UPDATE;

使用触发器,需要MySQL5或之后的版本支持。

一、触发器基本操作

1、创建触发器

创建触发器时,需要给出4条信息:

唯一的触发器名;(虽然MySQL5允许不同的表上的触发器名称相同,但一般最好不要这么做。)

触发器关联的表;

触发器响应的事件;

触发器何时执行;

语法结构:

create trigger trigger_name (BEFORE|AFTER) (delete|update|insert) on table_name

for each row

BEGIN

要触发的sql语句;

END;

示例:

[sql]

mysql> delimiter |

mysql> create trigger t_trig before insert on t_goods for each row

-> begin

-> set NEW.add_date = current_date();

-> end;

-> |

mysql> delimiter ;

mysql> insert into t_goods(id,goods_name,quantity)

-> values(1,'apple',50);

-> |

mysql> select * from t_goods;

-> |

+------+------------+----------+------------+

| id | goods_name | quantity | add_date |

+------+------------+----------+------------+

| 1 | apple | 50 | 2012-12-12 |

+------+------------+----------+------------+

2、删除触发器

DROP TRIGGER [schema_name.]trigger_name;

示例:

mysql> DROP TRIGGER t_trig;

3、查看触发器

SHOW TRIGGERS [ FROM DBNAME [ like '' ] ];

示例:

[sql]

mysql> show triggers;

+---------+--------+---------+------------------------------------------------------------------------------------------

| Trigger | Event | Table | Statement

+---------+--------+---------+------------------------------------------------------------------------------------------

| t_trig | INSERT | t_goods | begin

set NEW.add_date = current_date();

end | BEFORE | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |

+---------+--------+---------+------------------------------------------------------------------------------------------

mysql> show triggers from test;

+---------+--------+---------+------------------------------------------------------------------------------------------

| Trigger | Event | Table | Statement

+---------+--------+---------+------------------------------------------------------------------------------------------

| t_trig | INSERT | t_goods | begin

set NEW.add_date = current_date();

end | BEFORE | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |

+---------+--------+---------+------------------------------------------------------------------------------------------

mysql> show triggers from test like 't%';

+---------+--------+---------+------------------------------------------------------------------------------------------

| Trigger | Event | Table | Statement

+---------+--------+---------+------------------------------------------------------------------------------------------

| t_trig | INSERT | t_goods | begin

set NEW.add_date = current_date();

end | BEFORE | NULL | | root@localhost | latin1 | latin1_swedish_ci | latin1_swedish_ci |

+---------+--------+---------+------------------------------------------------------------------------------------------

二、注意事项

1、只有表支持触发器,视图及临时表都不支持;

2、每个表最多支持6个触发器;

3、单一触发器不能与多个事件或多个表关联;

4、对于INSERT而言,新增的行用NEW来表示,行中的每一列的值,用NEW.列名来表示;

对于DELETE而言,删除的行用OLD来表示;

对于UPDATE而言,更新前的行用OLD来表示,更新后的行用NEW来表示

bitsCN.com

人气教程排行