未加星标

触发器数据库

字体大小 | |
[数据库(综合) 所属分类 数据库(综合) | 发布者 店小二05 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

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

注:触发器只能创建在永久表上,不能对临时表创建触发器。

对同一个表相同触发器的相同触发事件,只能定义一个触发器。例如:对某个表的不同字段的after更新触发器,在使用oracle数据库的时候可以定义成两个不同的update触发器,更新不同的字段时触发单独的触发器,但在mysql数据库中,只能定义成一个触发器,在触发器中通过判断更新的字段进行对应处理。

使用别名old和new来引用触发器中发生变化的记录内容,这与其他的数据库是相似的,现在的触发器还支持行级触发,不支持语句级触发。

例子:

创建表t14,t15,并在表t14创建触发器,当向表t14插入数据的时候,也向表t15插入数据。

mysql> create table t14(id int(11),name varchar(20),age int(3));
Query OK, 0 rows affected (0.31 sec)
mysql> create table t15(id int(11),grade int(3));
Query OK, 0 rows affected (0.28 sec)
mysql> delimiter $$
mysql> create trigger ins_t14
-> after insert on t14 for each row begin
-> insert into t15(id,grade)values(1,1);
-> end;$$
Query OK, 0 rows affected (0.06 sec)
mysql> delimiter ;
mysql> insert into t14(id,name,age)values(1,'fzy1',1);
Query OK, 1 row affected (0.07 sec)
mysql> select * from t15;
+------+-------+
| id | grade |
+------+-------+
| 1 | 1 |
+------+-------+
1 row in set (0.00 sec)
由此,我们可以看到,当向t14插入数据的时候,确实触发了事件。

注: “delimiter $$ ” 语句用于更改结束符号。本来是以;为一个语句结束,现在改为“$$”作为结束,记得最后将“$$”改为;。

对于Insert into ...on duplicate key update...语句来说,触发器的顺序可能会造成疑惑,下面对t14分别创建before insert ,after insert ,before update ,after update触发器,然后插入记录,观察触发器的触发情况。

我们先删除ins_t14这个触发器:

mysql> drop trigger ins_t14;
Query OK, 0 rows affected (0.00 sec)
然后分别创建以上几种触发器:
mysql> delimiter $$
mysql> create trigger ins_t14_before
-> before insert on t14 for each row begin
-> insert into t15(text)values('before insert');
-> end;
-> $$
Query OK, 0 rows affected (0.10 sec)
mysql> create trigger ins_t14_aft
-> after insert on t14 for each row begin
-> insert into t15(text)values('after insert');
-> end;
-> $$
Query OK, 0 rows affected (0.06 sec)
mysql> create trigger upd_t14_bef
-> before update on t14 for each row begin
-> insert into t15(text)values('before update');
-> end;
-> $$
Query OK, 0 rows affected (0.12 sec)
mysql> create trigger upd_t14_aft
-> after update on t14 for each row begin
-> insert into t15(text)values('after update');
-> end;
-> $$
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t14(id,name,age)values(2,'fzy2',2);
Query OK, 1 row affected (0.04 sec)
mysql> select * from t15;
+------+-------+---------------+
| id | grade | text |
+------+-------+---------------+
| NULL | NULL | before insert |
| NULL | NULL | after insert |
+------+-------+---------------+
2 rows in set (0.00 sec)
mysql> update t14 set age=3 where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from t15;
+------+-------+---------------+
| id | grade | text |
+------+-------+---------------+
| NULL | NULL | before insert |
| NULL | NULL | after insert |
| NULL | NULL | before update |
| NULL | NULL | after update |
+------+-------+---------------+
4 rows in set (0.00 sec)

二,删除触发器

上面例子中已经使用过了删除触发器,之间考下来看一下应该就能明白了:

mysql> drop trigger ins_t14;
Query OK, 0 rows affected (0.00 sec)

三,查看触发器

可通过show triggers命令来查看触发器的状态,语法等信息,但因为不能查询指定的触发器,所以每次返回所有的触发器信息,使用起来不是很方便

mysql> show triggers \G;
*************************** 1. row *************************** Trigger: ins_t14_before Event: INSERT Table: t14
Statement: begin insert into t15(text)values('before insert'); end Timing: BEFORE Created: 2016-10-04 03:32:51.04sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: [email protected]
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 2. row *************************** Trigger: ins_t14_aft Event: INSERT Table: t14
Statement: begin insert into t15(text)values('after insert'); end Timing: AFTER Created: 2016-10-04 03:33:20.82sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: [email protected]
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 3. row *************************** Trigger: upd_t14_bef Event: UPDATE Table: t14
Statement: begin
insert into t15(text)values('before update');
end Timing: BEFORE Created: 2016-10-04 03:30:33.36sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: [email protected]
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
*************************** 4. row *************************** Trigger: upd_t14_aft Event: UPDATE Table: t14
Statement: begin
insert into t15(text)values('after update');
end Timing: AFTER Created: 2016-10-04 03:34:39.65sql_mode: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION Definer: [email protected]
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: latin1_swedish_ci
4 rows in set (0.00 sec)
ERROR:
No query specified
另一种方法是使用information_shema.triggers这个表,这个方式查询指定触发器信息,操作起来要方便很多
mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> desc triggers;
+----------------------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------------------+---------------+------+-----+---------+-------+
| TRIGGER_CATALOG | varchar(512) | NO | | | |
| TRIGGER_SCHEMA | varchar(64) | NO | | | |
| TRIGGER_NAME | varchar(64) | NO | | | |
| EVENT_MANIPULATION | varchar(6) | NO | | | |
| EVENT_OBJECT_CATALOG | varchar(512) | NO | | | |
| EVENT_OBJECT_SCHEMA | varchar(64) | NO | | | |
| EVENT_OBJECT_TABLE | varchar(64) | NO | | | |
| ACTION_ORDER | bigint(4) | NO | | 0 | |
| ACTION_CONDITION | longtext | YES | | NULL | |
| ACTION_STATEMENT | longtext | NO | | NULL | |
| ACTION_ORIENTATION | varchar(9) | NO | | | |
| ACTION_TIMING | varchar(6) | NO | | | |
| ACTION_REFERENCE_OLD_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_NEW_TABLE | varchar(64) | YES | | NULL | |
| ACTION_REFERENCE_OLD_ROW | varchar(3) | NO | | | |
| ACTION_REFERENCE_NEW_ROW | varchar(3) | NO | | | |
| CREATED | datetime(2) | YES | | NULL | |
| SQL_MODE | varchar(8192) | NO | | | |
| DEFINER | varchar(93) | NO | | | |
| CHARACTER_SET_CLIENT | varchar(32) | NO | | | |
| COLLATION_CONNECTION | varchar(32) | NO | | | |
| DATABASE_COLLATION | varchar(32) | NO | | | |
+----------------------------+---------------+------+-----+---------+-------+
22 rows in set (0.00 sec)
mysql> select * from triggers \G;
*************************** 1. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: sys TRIGGER_NAME: sys_config_insert_set_user
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-09-25 19:38:11.69 SQL_MODE: DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 2. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: sys TRIGGER_NAME: sys_config_update_set_user
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: sys
EVENT_OBJECT_TABLE: sys_config ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: BEGIN IF @sys.ignore_sys_config_triggers != true AND NEW.set_by IS NULL THEN SET NEW.set_by = USER(); END IF; END
ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-09-25 19:38:11.79 SQL_MODE: DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: utf8_general_ci
*************************** 3. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: test1 TRIGGER_NAME: ins_t14_before
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14 ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin insert into t15(text)values('before insert'); end
ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-10-04 03:32:51.04 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 4. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: test1 TRIGGER_NAME: ins_t14_aft
EVENT_MANIPULATION: INSERT
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14 ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin insert into t15(text)values('after insert'); end
ACTION_ORIENTATION: ROW ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-10-04 03:33:20.82 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 5. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: test1 TRIGGER_NAME: upd_t14_bef
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14 ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into t15(text)values('before update');
end
ACTION_ORIENTATION: ROW ACTION_TIMING: BEFORE
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-10-04 03:30:33.36 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
*************************** 6. row ***************************
TRIGGER_CATALOG: defTRIGGER_SCHEMA: test1 TRIGGER_NAME: upd_t14_aft
EVENT_MANIPULATION: UPDATE
EVENT_OBJECT_CATALOG: def
EVENT_OBJECT_SCHEMA: test1
EVENT_OBJECT_TABLE: t14 ACTION_ORDER: 1
ACTION_CONDITION: NULL
ACTION_STATEMENT: begin
insert into t15(text)values('after update');
end
ACTION_ORIENTATION: ROW ACTION_TIMING: AFTER
ACTION_REFERENCE_OLD_TABLE: NULL
ACTION_REFERENCE_NEW_TABLE: NULL
ACTION_REFERENCE_OLD_ROW: OLD
ACTION_REFERENCE_NEW_ROW: NEW CREATED: 2016-10-04 03:34:39.65 SQL_MODE: ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION DEFINER: [email protected]
CHARACTER_SET_CLIENT: utf8
COLLATION_CONNECTION: utf8_general_ci
DATABASE_COLLATION: latin1_swedish_ci
6 rows in set (0.01 sec)
ERROR:
No query specified
四,触发器的使用

触发器的执行有以下两个限制

1,触发程序不能调用将数据返回客户端的存储程序,也不能采用call语句的动态sql语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过out或者inout类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。

2,不能在触发器中使用以显式或隐式方式开始或结束事务的语句,如start transction ,commit 或rollback。

本文数据库(综合)相关术语:系统安全软件

主题: TISQLCTIIGG数据UTAUFUSUAUT
分页:12
转载请注明
本文标题:触发器数据库
本站链接:http://www.codesec.net/view/479883.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 数据库(综合) | 评论(0) | 阅读(60)