mysql——觸發器

Jelly_lyj發表於2017-03-18
MySql觸發器

觸發器的含義與作用

觸發器(trigger)是由事件來觸發某個操作，主要是由insert update delete等事件來觸發某種特定的條件；

滿足觸發器的觸發條件時，資料庫就會執行觸發器定義的程式語句，

比如：當學生表當中增加了一個學生記錄，學生的總數就必須同時改變。可以在這裡建立一個觸發器，每次增加一個學生的記錄。就執行一次計算學生的總數量的操作。這可以保證每次增加學生後的記錄統計一直保持最新。

 

觸發器的定義語句

# 單個執行語句的觸發器

create trigger 觸發器名 after|before 觸發事件
on 表名 for each row
執行語句

# 多個執行語句的觸發器

delimiter &&   //把分隔符改成&&(當然了可以自定義為其他的)(注意有空格)

create trigger 觸發器名 after|before 觸發事件  //after表示在觸發事件之後執行語句；before表示在之前執行語句
on 表名 for each row         //on表示在哪張表之上
begin
執行語句1;
執行語句2;
........;
end &&        //以&&表示結束這條語句

delimiter ;   //把分隔符改回來(注意有空格)

 

 

以一個簡單的例子理解觸發器

A表 <--- 觸發器t1 ---> B表
當A表insert一條記錄之後(觸發事件),
觸發器將統計A表中的總記錄數(執行語句1),並寫入B表中(執行語句2),

當使用者每次想要得到A表的結果(總記錄數)，只需要到B表去查，從而避免了#select count(*) from A;這條語句的查詢負擔

#建立A表
create table A(
      userid int(10),
      username varchar(20),
      old int(4),
      address varchar(30));

#建立B表
create table B(
    insert_time time,   //insert_time屬性用於記錄insert的時間
    count int);         //count屬性用於記錄A表的總記錄數

#建立觸發器t1的過程如下
delimiter &&

create trigger t1 after insert 
    on A for each row
    begin
    declare acount int;
    set acount=( select count(*) from A );
    insert into B values(now(),acount);
    end &&

delimiter ;

#驗證:
insert into A values('008','lyj88','100','gz');
......

select * from B;
+-------------+-------+
| insert_time | count |
+-------------+-------+
| 00:41:39    |     5 |
+-------------+-------+

 

 

檢視觸發器資訊的語句

#查詢當前資料庫中的所有觸發器資訊
show triggers \G;

#查詢全部觸發器的詳細資訊
(所有觸發器的資訊都存在information_schema庫中的triggers表裡面)
use information_schema;
select * from information_schema.triggers \G;

#查詢單個觸發器的詳細資訊
select * from triggers where trigger_name='product_af_delete'\G;

 

刪除觸發器的語句

drop trigger 觸發器名;

 

 

一道練習題

 

#建立product表
create table product(
id int(10) primary key not null unique,
name varchar(20) not null,
function varchar(50),
company varchar(20) unique,
address varchar(50)
);

#建立operate表
create table operate(
op_id int(10) not null,
op_type varchar(20) not null,
op_time time not null
);

#建立 product_bf_insert 觸發器
delimiter &&
create trigger product_bf_insert before insert 
on product for each row
begin
declare count1 int(10);
set count1=( select count(*) from product );
insert into operate values(count1,'insert',now());
end &&
delimiter ;

#建立product_af_update觸發器
delimiter &&
create trigger product_af_update after update
on product for each row
begin
declare count2 int(10);
set count2=( select count(*) from product );
insert into operate values(count2,'update',now());
end &&
delimiter ;

#建立product_af_delete觸發器
delimiter &&
create trigger product_af_delete after delete 
on product for each row
begin
declare count3 int(10);
set count3=( select count(*) from product );
insert into operate values(count3,'delete',now());
end &&
delimiter ;

#執行inset、update、delete語句，可以在operate表中查詢到相關的記錄
insert into product values(100,'p2','ko','google','ch');
insert into product values(101,'p2','ko','baidu','ch');
update product set function='ok' where id=100;
delete from product where id=101;

select * from operate;
+-------+---------+----------+
| op_id | op_type | op_time  |
+-------+---------+----------+
|     0 | insert  | 01:54:00 |
|     1 | insert  | 03:15:32 |
|     2 | update  | 03:21:44 |
|     1 | delete  | 03:28:27 |
+-------+---------+----------+

 

相關文章