# 【mysql的編程專題②】觸發器 標簽（空格分隔）： Mysql --- 類似tp里面的數據模型回調接口,在數據表增刪改的前或后觸發執行其他的預訂的sql; 一個觸發器要具備4要素: 1.監視地點 -- 要執行觸發器的表 2.監視事件 -- 由什么DML事件來牽引 3.觸發時間 -- 是在DML事件發生的前或后 4.觸發事件 -- 要觸發執行的預訂sql,也是DML ## 創建觸發器 ```sql create trigger <觸發器名稱> { before | after} {insert | update | delete} on <表名> for each row <觸發器SQL語句> ``` ### 實例 ```sql -- 創建練習需要用的兩張表 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 ; ``` ### 項目中用到實例 ```sql 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 ``