mysql触发器的使用

mysql触发器  

1.创建数据库my

 

mysql>create database my;  

 

2.打开数据库my

mysql>use my

 3.  在数据库my 中创建数据表depts

mysql>create table depts(id int(11),name varchar(20),description varchar(255));

4. 在数据库表depts 中插入记录

mysql>insert into depts values(1,' 人事部',null),(2,' 研发部',null),(3,' 市场部',null),(4,' 培训部',null);

5. 显示数据库表

mysql>desc depts;

 6. 查询插入的记录

Mysql>select * from depts;

 7. 创建一个储存数据的表test

mysql>create table test(user_id varchar(20),description varchar(50));

8. 显示创建的数据库表test

Mysql>desc test;

9 ．查询test 表是空的

Mysql>select * from test;

 

6. 创建删除记录触发器

mysql>delimiter $$ 

mysql>create trigger testref  before delete on depts for each row    begin insert into test(user_id,description) values(user(),'depts delete');

mysql>  end;

msyql>$$

 7. 删除记录

mysql>delimiter ;  // 以分号结束语句

mysql>delete from depts where name=' 人事部';

8. 查看返回数据表test 的数据

mysql>select * from test;

 

9. 创建删除触发器

mysql>delimiter $$

mysql>create trigger testref1 after update on depts for each row

mysql>begin

mysql>insert into test(user_id,description) values(user(),'depts update'); 

mysql>end; 

mysql>$$

 10. 更新记录

mysql>delimiter ;

mysql>update depts set id=8 where name=' 研发部';

mysql>select * from test;

 11. 创建插入触发器

mysql>delimiter $$

mysql>create trigger testref2 before insert on depts for each row begin  insert into test(user_id,description) values(user(),’depts insert’);

mysql>end;

msyql>$$

 

12. 插入记录

mysql>delimiter ; 

mysql>insert into depts values(7,’ 资源部’,null); 

13. 查看返回表记录

mysql>select * from test;

 

14. 查看创建的触发器

mysql>show triggers;

 

 15. 删除触发器

Mysql>drop trigger testref;

Mysql>drop trigger testref1;

http://chenboqiang.blog.51cto.com/1068057/267415

本文讲述MySql5.x中触发器的使用，包括创建触发器、删除触发器和查询触发器的相关知识，对其语法、使用举例和注意事项进行了详细说明。

一．             触发器概述

       触发器是一种特殊类型的存储过程，它主要是通过事件进行触发而被执行的（存储过程可以通过存储过程名字而被直接调用）。

当对某一表进行诸如UPDATE、 INSERT、 DELETE 这些操作时，就会自动执行触发器所定义的SQL 语句。

触发器的主要作用是实现由主键和外键所不能保证的复杂的参照完整性和数据的一致性。除此之外，触发器还有其它许多不同的功能：

（1）强化约束

触发器能够实现比CHECK 语句更为复杂的约束。

（2） 跟踪变化

触发器可以侦测数据库内的操作，从而不允许数据库中未经许可的指定更新和变化。

（3） 级联运行

触发器可以侦测数据库内的操作，并自动地级联影响整个数据库的各项内容。例如，某个表上的触发器中包含有对另外一个表的数据操作（如删除，更新，插入）而该操作又导致该表上触发器被触发。

（4） 存储过程的调用

为了响应数据库更，触发器可以调用一个或多个存储过程，甚至可以通过外部过程的调用而在DBMS（ 数据库管理系统）本身之外进行操作

总体而言，触发器性能通常比较低。当运行触发器时，系统处理的大部分时间花费在参照其它表的这一处理上，因为这些表既不在内存中也不在数据库设备上，而删除表和插入表总是位于内存中。可见触发器所参照的其它表的位置决定了操作要花费的时间长短。

在实际的项目中，不推荐使用触发器。

二．             创建触发器——CREATE TRIGGER

1.       语法

CREATE TRIGGER trigger_name trigger_time trigger_event

    ON tbl_name FOR EACH ROW trigger_stmt

触发程序是与表有关的命名数据库对象，当表上出现特定事件时，将激活该对象。

触发程序与命名为tbl_name的表相关，而且这个相关表必须引用永久性表，不能与临时表或视图关联起来。

（1）      trigger_name

触发器的名称。

（2）      trigger_time

触发器的动作时间，可在事件之前（BEFORE）或事件之后（AFTER）。

（3）      trigger_event

可为INSERT、UPDATE和DELETE，分别表示在插入、更新和删除新行时激活触发程序。

（4）      tbl_name

与该触发器相关的永久性表。

（5）      trigger_stmt

触发程序激活时执行的语句。若有多个执行语句时，可使用BEGIN……END复合语句结构。

2.       使用举例

Eg1. 在本实例中用触发器实现级联删除的效果。本实例创建两个表：t_organization（组织表）和t_user（用户表），其中用户表的org_id与组织表的主键建立外键关联，因此，在删除组织表中的某记录时，若用户表存在关联记录时，将会删除失败。触发器trg_del_org_user用于在用户删除组织表的记录前，首先删除用户表的对应记录，因此trigger_time为BEFORE，trigger_event为DELETE。

DROP TABLE IF EXISTS t_user;

DROP TABLE IF EXISTS t_organization;

CREATE TABLE t_organization

(

org_id INT NOT NULL PRIMARY KEY,

name VARCHAR(50) NOT NULL,

    description VARCHAR(255),

    gen_time DATETIME NOT NULL

);

INSERT INTO t_organization VALUES(1, 'test ', NULL, NOW());

CREATE TABLE t_user

(

user_id VARCHAR(50) NOT NULL PRIMARY KEY,

name VARCHAR(50) NOT NULL,

org_id INT NOT NULL,

description VARCHAR(255),

gen_time DATETIME NOT NULL,

constraint fk_org_id_user_org foreign key (org_id)

      references t_organization (org_id) on delete restrict on update restrict

   );

   INSERT INTO t_user VALUES('amigo ', 'xiexingxing', 1, NULL, NOW());

delimiter $$

CREATE TRIGGER trg_del_org_user BEFORE DELETE ON t_organization

 FOR EACH ROW BEGIN

     DELETE FROM t_user WHERE t_user.org_id = OLD.org_id;

END$$

delimiter ;

其中delimiter用来更改结束符，上例中用“$$”作为结束符，“END$$”中的“$$”表示触发器执行结束，最后一句“delimiter ;”将结束符改成默认的“;”。

3.       注意事项

（1）trigger_event能被LOAD DATE语句激活；

（2）不能对某个表具有两个或多个具有相同的动作时间和事件的触发程序；

（3）触发程序名称存在于方案的名称空间内，这意味着，在1个方案中，所有的触发程序必须具有唯一的名称。位于不同方案中的触发程序可以具有相同的名称；

（4）触发程序不能调用将数据返回客户端的存储程序，也不能使用采用CALL语句的动态SQL；

（5）触发程序不能使用以显式或隐式方式开始或结束事务的语句，如START TRANSACTION、COMMIT或ROLLBACK；

（6）在INSERT触发程序中，仅能使用NEW.col_name，没有旧行。在DELETE触发程序中，仅能使用OLD.col_name，没有新行。在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列；

（7）用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值；

（8）在BEFORE触发程序中，AUTO_INCREMENT列的NEW值为0，不是实际插入新记录时将自动生成的序列号。

三．             删除触发器——DROP TRIGGER

1.       语法

DROP TRIGGER [schema_name.]trigger_name

该语句用户删除触发器，[schema_name.]用于指定所在的数据库的名称，若未加，则默认为当前选中的数据库。

2.       使用举例

Eg. 删除在前面的小节中创建的触发器trg_del_org_user：

DROP TRIGGER trg_del_org_user;

3.       注意事项

DROP TRIGGER语句需要SUPER权限。

四．             查询触发器——SHOW TRIGGERS

1.       语法

SHOW TRIGGERS;

该语句用于查询触发器。

2.       使用举例

运行：SHOW TRIGGERS;

输出的主要信息如下：

| Trigger  | Event | Table | Statement | Timing | Created | sql_mode

| trg_del_org_user | DELETE | t_organization | BEGIN

     DELETE FROM t_user WHERE t_user.org_id = OLD.org_id;

END | BEFORE | NULL    | STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBST

ITUTION |

参考文章：《MySql5.1参考手册》

         《触发器概述》

          触发器小例子