mysql牵引_【mysql的编程专题②】触发器

类似tp里面的数据模型回调接口,在数据表增删改的前或后触发执行其他的预订的sql;

一个触发器要具备4要素:

1.监视地点 -- 要执行触发器的表

2.监视事件 -- 由什么DML事件来牵引

3.触发时间 -- 是在DML事件发生的前或后

4.触发事件 -- 要触发执行的预订sql,也是DML

创建触发器

create trigger

{ before | after}

{insert | update | delete}

on

for each row

实例

-- 创建练习需要用的两张表

CREATE TABLE `orders` (

`oid` int(10) unsigned NOT NULL AUTO_INCREMENT,

`gid` tinyint(4) DEFAULT NULL,

`num` smallint(5) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`oid`),

KEY `gid` (`gid`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `goods` (

`gid` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,

`gname` varchar(100) NOT NULL DEFAULT '',

`stock` smallint(5) unsigned NOT NULL DEFAULT '0',

PRIMARY KEY (`gid`),

KEY `stock` (`stock`)

) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- 为商品表插入数据

INSERT INTO `goods`(`gname`,`stock`) VALUES('电脑',45),('自行车',50),('汽车',100),('手机',500);

-- 创建触发器 tg1,监视订单表,当订单表增加订单时,商品表就得减少;

DELIMITER $

CREATE TRIGGER `tg1`

AFTER INSERT ON `orders` -- 在订单表发生插入时

FOR EACH ROW -- 每一行数据的插入都要触发TRIGGER

BEGIN -- 开始书写预订要被触发的SQL

UPDATE `goods` SET `stock` = `stock` - `new`.`num` WHERE `gid` = `new`.`gid`; -- new.num就是新增的订单表row的值;由于此处预订的sql是以分号结尾,所以再创建trigger之初就得把mysql默认的分号结束符改为其他的,否则mysql就会以为到此句就结束,于是报错;

END $

DELIMITER ; -- 更改回来;

-- 创建触发器 tg2,监视订单表,当订单被删掉时,商品表增加;

DELIMITER $

CREATE TRIGGER `tg2`

AFTER DELETE ON `orders`

FOR EACH ROW

BEGIN

UPDATE `goods` SET `stock` = `stock` + `old`.`num` WHERE `gid` = `old`.`gid`;

END $

DELIMITER ;

-- 创建触发器 tg3,监视订单表,在订单增加前判断是否大于5,如果大于5,就让其等于5(限购5个);

DELIMITER $

CREATE TRIGGER `tg3`

BEFORE INSERT ON `orders`

FOR EACH ROW

BEGIN

IF `new`.`num` > 5

THEN

SET `new`.`num` = 5;

END IF;-- 在begin内每句要执行的sql都要带分号结束,这一个判断语句其实是指定了两句sql

UPDATE `goods` SET `stock` = `stock` - `new`.`num` WHERE `gid` = `new`.`gid`;

END $

DELIMITER ;

项目中用到实例

delimiter $

CREATE TRIGGER sum_rebate_tg

after update on sc_supplier_rebate

for each row

begin

if new.is_confirm = 1

then

set @fl = new.consume_discount + new.recommend_discount;

update sc_supplier_average_score set sum_rebate = @fl where supplier_id = new.supplier_id;

elseif new.is_confirm = 0

THEN

update sc_supplier_average_score set sum_rebate = 0 where supplier_id = new.supplier_id;

end if;

end $

delimiter ;

关于new和old

INSERT 和 DELETE 事件只能用old代表原来的列值集合,用点语法取出每列的值,如old.num(取出以前num列里面的值)

在UPDATE触发程序中，可以使用OLD.col_name来引用更新前的某一行的列，也能使用NEW.col_name来引用更新后的行中的列。

用OLD命名的列是只读的。你可以引用它，但不能更改它。对于用NEW命名的列，如果具有SELECT权限，可引用它。在BEFORE触发程序中，如果你具有UPDATE权限，可使用“SET NEW.col_name = value”更改它的值。这意味着，你可以使用触发程序来更改将要插入到新行中的值，或用于更新行的值。

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

删除触发器

DROP TRIGGER [schema_name.]trigger_name

舍弃触发程序。方案名称(schema_name)是可选的。如果省略了schema(方案)，将从当前方案中舍弃触发程序。

DROP TRIGGER test.num