Chapter聽21.聽Triggers - MySQL 5.1参考手册中文版
MySQL 5.1鍖呭惈瀵硅Е鍙戠▼搴忕殑鏀寔銆傝Е鍙戠▼搴忔槸涓庤〃鏈夊叧鐨勫懡鍚嶆暟鎹簱瀵硅薄锛屽綋琛ㄤ笂鍑虹幇鐗瑰畾浜嬩欢鏃讹紝灏嗘縺娲昏瀵硅薄銆備緥濡傦紝涓嬭堪璇彞灏嗗垱寤1涓〃鍜1涓INSERT瑙﹀彂绋嬪簭銆瑙﹀彂绋嬪簭灏嗘彃鍏ヨ〃涓煇涓鍒楃殑鍊煎姞鍦ㄤ竴璧凤細
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
鍦ㄦ湰绔犱腑锛屼粙缁嶄簡鍒涘缓鍜屾挙閿瑙﹀彂绋嬪簭鐨勮娉曪紝骞剁粰鍑轰簡涓浜涗娇鐢ㄥ畠浠殑绀轰緥銆傚叧浜庡瑙﹀彂绋嬪簭浣跨敤鐨勯檺鍒讹紝璇峰弬瑙闄勫綍I锛鐗规ч檺鍒銆
鍏充簬瑙﹀彂绋嬪簭鐨勪簩杩涘埗鏃ュ織鍔熻兘锛岃鍙傝20.4鑺傦紝鈥滃瓨鍌ㄥ瓙绋嬪簭鍜岃Е鍙戠▼搴忕殑浜岃繘鍒舵棩蹇楀姛鑳解銆
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt
瑙﹀彂绋嬪簭鏄笌琛ㄦ湁鍏崇殑鍛藉悕鏁版嵁搴撳璞★紝褰撹〃涓婂嚭鐜扮壒瀹氫簨浠舵椂锛屽皢婵娲昏瀵硅薄銆
瑙﹀彂绋嬪簭涓庡懡鍚嶄负tbl_name鐨勮〃鐩稿叧銆tbl_name蹇呴』寮曠敤姘镐箙鎬ц〃銆備笉鑳藉皢瑙﹀彂绋嬪簭涓TEMPORARY琛ㄦ垨瑙嗗浘鍏宠仈璧锋潵銆
trigger_time鏄Е鍙戠▼搴忕殑鍔ㄤ綔鏃堕棿銆傚畠鍙互鏄BEFORE鎴AFTER锛屼互鎸囨槑瑙﹀彂绋嬪簭鏄湪婵娲诲畠鐨勮鍙ヤ箣鍓嶆垨涔嬪悗瑙﹀彂銆
trigger_event鎸囨槑浜嗘縺娲昏Е鍙戠▼搴忕殑璇彞鐨勭被鍨嬨trigger_event鍙互鏄笅杩板间箣涓锛
路 INSERT锛氬皢鏂拌鎻掑叆琛ㄦ椂婵娲昏Е鍙戠▼搴忥紝渚嬪锛岄氳繃INSERT銆LOAD DATA鍜REPLACE璇彞銆
路 UPDATE锛氭洿鏀规煇涓琛屾椂婵娲昏Е鍙戠▼搴忥紝渚嬪锛岄氳繃UPDATE璇彞銆
路 DELETE锛氫粠琛ㄤ腑鍒犻櫎鏌愪竴琛屾椂婵娲昏Е鍙戠▼搴忥紝渚嬪锛岄氳繃DELETE鍜REPLACE璇彞銆
璇锋敞鎰忥紝trigger_event涓庝互琛ㄦ搷浣滄柟寮忔縺娲昏Е鍙戠▼搴忕殑SQL璇彞骞朵笉寰堢被浼硷紝杩欑偣寰堥噸瑕併備緥濡傦紝鍏充簬INSERT鐨BEFORE瑙﹀彂绋嬪簭涓嶄粎鑳借INSERT璇彞婵娲伙紝涔熻兘琚LOAD DATA璇彞婵娲汇
鍙兘浼氶犳垚娣锋穯鐨勪緥瀛愪箣涓鏄INSERT INTO .. ON DUPLICATE UPDATE ...璇硶锛BEFORE INSERT瑙﹀彂绋嬪簭瀵逛簬姣忎竴琛屽皢婵娲伙紝鍚庤窡AFTER INSERT瑙﹀彂绋嬪簭锛屾垨BEFORE UPDATE鍜AFTER UPDATE瑙﹀彂绋嬪簭锛屽叿浣撴儏鍐靛彇鍐充簬琛屼笂鏄惁鏈夐噸澶嶉敭銆
瀵逛簬鍏锋湁鐩稿悓瑙﹀彂绋嬪簭鍔ㄤ綔鏃堕棿鍜屼簨浠剁殑缁欏畾琛紝涓嶈兘鏈変袱涓Е鍙戠▼搴忋備緥濡傦紝瀵逛簬鏌愪竴琛紝涓嶈兘鏈変袱涓BEFORE UPDATE瑙﹀彂绋嬪簭銆浣嗗彲浠ユ湁1涓BEFORE UPDATE瑙﹀彂绋嬪簭鍜1涓BEFORE INSERT瑙﹀彂绋嬪簭锛屾垨1涓BEFORE UPDATE瑙﹀彂绋嬪簭鍜1涓AFTER UPDATE瑙﹀彂绋嬪簭銆
trigger_stmt鏄綋瑙﹀彂绋嬪簭婵娲绘椂鎵ц鐨勮鍙ャ傚鏋滀綘鎵撶畻鎵ц澶氫釜璇彞锛屽彲浣跨敤BEGIN ... END澶嶅悎璇彞缁撴瀯銆傝繖鏍凤紝灏辫兘浣跨敤瀛樺偍瀛愮▼搴忎腑鍏佽鐨勭浉鍚岃鍙ャ傝鍙傝20.2.7鑺傦紝鈥淏EGIN ... END澶嶅悎璇彞鈥銆
鍦MySQL 5.1涓紝鍙互缂栧啓鍖呭惈鎸夊悕绉板琛ㄨ繘琛岀洿鎺ュ紩鐢ㄧ殑瑙﹀彂绋嬪簭锛屽涓嬩緥涓墍绀虹殑鍚嶄负testref鐨勮Е鍙戠▼搴忥細
CREATE TABLE test1(a1 INT);
CREATE TABLE test2(a2 INT);
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
CREATE TABLE test4(
a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
b4 INT DEFAULT 0
);
DELIMITER |
CREATE TRIGGER testref BEFORE INSERT ON test1
FOR EACH ROW BEGIN
INSERT INTO test2 SET a2 = NEW.a1;
DELETE FROM test3 WHERE a3 = NEW.a1;
UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
|
DELIMITER ;
INSERT INTO test3 (a3) VALUES
(NULL), (NULL), (NULL), (NULL), (NULL),
(NULL), (NULL), (NULL), (NULL), (NULL);
INSERT INTO test4 (a4) VALUES
(0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
濡傛灉灏嗕笅杩板兼彃鍏ヨ〃test1锛屽涓嬫墍绀猴細
mysql> INSERT INTO test1 VALUES
-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
閭ｄ箞4涓〃涓殑鏁版嵁濡備笅锛
mysql> SELECT * FROM test1;
+------+
| a1 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test2;
+------+
| a2 |
+------+
| 1 |
| 3 |
| 1 |
| 7 |
| 1 |
| 8 |
| 4 |
| 4 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM test3;
+----+
| a3 |
+----+
| 2 |
| 5 |
| 6 |
| 9 |
| 10 |
+----+
5 rows in set (0.00 sec)
mysql> SELECT * FROM test4;
+----+------+
| a4 | b4 |
+----+------+
| 1 | 3 |
| 2 | 0 |
| 3 | 1 |
| 4 | 2 |
| 5 | 0 |
| 6 | 0 |
| 7 | 1 |
| 8 | 1 |
| 9 | 0 |
| 10 | 0 |
+----+------+
10 rows in set (0.00 sec)
浣跨敤鍒悕OLD鍜NEW锛岃兘澶熷紩鐢ㄤ笌瑙﹀彂绋嬪簭鐩稿叧鐨勮〃涓殑鍒椼OLD.col_name鍦ㄦ洿鏂版垨鍒犻櫎瀹冧箣鍓嶏紝寮曠敤宸叉湁琛屼腑鐨1鍒椼NEW.col_name鍦ㄦ洿鏂板畠涔嬪悗寮曠敤灏嗚鎻掑叆鐨勬柊琛岀殑1鍒楁垨宸叉湁琛岀殑1鍒椼
婵娲昏Е鍙戠▼搴忔椂锛屽浜庤Е鍙戠▼搴忓紩鐢ㄧ殑鎵鏈OLD鍜NEW鍒楋紝闇瑕佸叿鏈SELECT鏉冮檺锛屽浜庝綔涓SET璧嬪肩洰鏍囩殑鎵鏈NEW鍒楋紝闇瑕佸叿鏈UPDATE鏉冮檺銆
娉ㄩ噴锛鐩墠锛岃Е鍙戠▼搴忎笉浼氳绾ц仈鐨 澶栭敭鍔ㄤ綔婵娲汇傝闄愬埗灏嗕細琚敖鏃╂斁瀹姐
CREATE TRIGGER璇彞闇瑕SUPER鏉冮檺銆
DROP TRIGGER [schema_name.]trigger_name
鑸嶅純瑙﹀彂绋嬪簭銆傛柟妗堝悕绉帮紙schema_name锛夋槸鍙夌殑銆傚鏋滅渷鐣ヤ簡schema锛堟柟妗堬級锛屽皢浠庡綋鍓嶆柟妗堜腑鑸嶅純瑙﹀彂绋嬪簭銆
娉ㄩ噴锛浠MySQL 5.0.10涔嬪墠鐨MySQL鐗堟湰鍗囩骇鍒5.0.10鎴栨洿楂樼増鏈椂锛堝寘鎷墍鏈夌殑MySQL 5.1鐗堟湰锛夛紝蹇呴』鍦ㄥ崌绾т箣鍓嶈垗寮冩墍鏈夌殑瑙﹀彂绋嬪簭锛屽苟鍦ㄩ殢鍚庨噸鏂板垱寤哄畠浠紝鍚﹀垯锛屽湪鍗囩骇涔嬪悗DROP TRIGGER涓嶅伐浣溿傚叧浜庢帹鑽愮殑鍗囩骇姝ラ锛岃鍙傝2.10.1鑺傦紝鈥滀粠5.0鐗堝崌绾р 銆
DROP TRIGGER璇彞闇瑕SUPER鏉冮檺銆
鍦ㄦ湰鑺備腑锛屼粙缁嶄簡鍦MySQL 5.1涓娇鐢ㄨЕ鍙戠▼搴忕殑鏂规硶锛屽苟浠嬬粛浜嗗湪浣跨敤瑙﹀彂绋嬪簭鏂归潰鐨勯檺鍒躲
瑙﹀彂绋嬪簭鏄笌琛ㄦ湁鍏崇殑鍛藉悕鏁版嵁搴撳璞★紝褰撹〃涓婂嚭鐜扮壒瀹氫簨浠舵椂锛屽皢婵娲昏瀵硅薄銆傚湪鏌愪簺瑙﹀彂绋嬪簭鐨勭敤娉曚腑锛屽彲鐢ㄤ簬妫鏌ユ彃鍏ュ埌琛ㄤ腑鐨勫硷紝鎴栧鏇存柊娑夊強鐨勫艰繘琛岃绠椼
瑙﹀彂绋嬪簭涓庤〃鐩稿叧锛屽綋瀵硅〃鎵цINSERT銆DELETE鎴UPDATE璇彞鏃讹紝灏嗘縺娲昏Е鍙戠▼搴忋傚彲浠ュ皢瑙﹀彂绋嬪簭璁剧疆涓哄湪鎵ц璇彞涔嬪墠鎴栦箣鍚庢縺娲汇渚嬪锛屽彲浠ュ湪浠庤〃涓垹闄ゆ瘡涓琛屼箣鍓嶏紝鎴栧湪鏇存柊浜嗘瘡涓琛屽悗婵娲昏Е鍙戠▼搴忋
瑕佹兂鍒涘缓瑙﹀彂绋嬪簭鎴栬垗寮冭Е鍙戠▼搴忥紝鍙娇鐢CREATE TRIGGER鎴DROP TRIGGER璇彞銆傚叧浜庤繖浜涜鍙ョ殑璇硶锛岃鍙傝21.1鑺傦紝鈥淐REATE TRIGGER璇硶鈥鍜21.2鑺傦紝鈥淒ROP TRIGGER璇硶鈥銆
涓嬮潰缁欏嚭浜1涓畝鍗曠殑绀轰緥锛屽湪璇ョず渚嬩腑锛岄拡瀵INSERT璇彞锛灏嗚Е鍙戠▼搴忓拰琛ㄥ叧鑱斾簡璧锋潵銆傚叾浣滅敤鐩稿綋浜庣疮鍔犲櫒锛岃兘澶熷皢鎻掑叆琛ㄤ腑鏌愪竴鍒楃殑鍊煎姞璧锋潵銆
鍦ㄤ笅闈㈢殑璇彞涓紝鍒涘缓浜1涓〃锛屽苟涓鸿〃鍒涘缓浜1涓Е鍙戠▼搴忥細
mysql> CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
mysql> CREATE TRIGGER ins_sum BEFORE INSERT ON account
-> FOR EACH ROW SET @sum = @sum + NEW.amount;
CREATE TRIGGER璇彞鍒涘缓浜嗕笌璐︽埛琛ㄧ浉鍏崇殑銆佸悕涓ins_sum鐨勮Е鍙戠▼搴忋傚畠杩樺寘鎷竴浜涘瓙鍙ワ紝杩欎簺瀛愬彞鎸囧畾浜嗚Е鍙戠▼搴忔縺娲绘椂闂淬佽Е鍙戠▼搴忎簨浠躲佷互鍙婃縺娲昏Е鍙戠▼搴忔椂浣滀簺浠涔堬細
路 鍏抽敭瀛BEFORE鎸囨槑浜瑙﹀彂绋嬪簭鐨勫姩浣滄椂闂淬傚湪鏈緥涓紝搴斿湪灏嗘瘡涓琛屾彃鍏ヨ〃涔嬪墠婵娲昏Е鍙戠▼搴忋傝繖绫诲厑璁哥殑鍏朵粬鍏抽敭瀛楁槸AFTER銆
路 鍏抽敭瀛INSERT鎸囨槑浜嗘縺娲昏Е鍙戠▼搴忕殑浜嬩欢銆鍦ㄦ湰渚嬩腑锛INSERT璇彞灏嗗鑷磋Е鍙戠▼搴忕殑婵娲汇備綘涔熷彲浠ヤ负DELETE鍜UPDATE璇彞鍒涘缓瑙﹀彂绋嬪簭銆
路 璺熷湪FOR EACH ROW鍚庨潰鐨勮鍙瀹氫箟浜嗘瘡娆℃縺娲昏Е鍙戠▼搴忔椂灏嗘墽琛岀殑绋嬪簭锛屽浜庡彈瑙﹀彂璇彞褰卞搷鐨勬瘡涓琛屾墽琛屼竴娆°傚湪鏈緥涓紝瑙﹀彂鐨勮鍙ユ槸绠鍗曠殑SET璇彞锛岃礋璐ｅ皢鎻掑叆amount鍒楃殑鍊煎姞璧锋潵銆傝璇彞灏嗗垪寮曠敤涓NEW.amount锛屾剰鎬濇槸鈥灏嗚鎻掑叆鍒版柊琛岀殑amount鍒楃殑鍊鈥銆
瑕佹兂浣跨敤瑙﹀彂绋嬪簭锛屽皢绱姞鍣ㄥ彉閲忚缃负0锛屾墽琛INSERT璇彞锛岀劧鍚庢煡鐪嬪彉閲忕殑鍊硷細
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
鍦ㄦ湰渚嬩腑锛屾墽琛屼簡INSERT璇彞鍚庯紝@sum鐨勫兼槸14.98 + 1937.50 鈥 100锛鎴1852.48銆
瑕佹兂閿姣佽Е鍙戠▼搴忥紝鍙娇鐢DROP TRIGGER璇彞銆傚鏋滆Е鍙戠▼搴忎笉鍦ㄩ粯璁ょ殑鏂规涓紝蹇呴』鎸囧畾鏂规鍚嶇О锛
mysql> DROP TRIGGER test.ins_sum;
瑙﹀彂绋嬪簭鍚嶇О瀛樺湪浜庢柟妗堢殑鍚嶇О绌洪棿鍐咃紝杩欐剰鍛崇潃锛屽湪1涓柟妗堜腑锛屾墍鏈夌殑瑙﹀彂绋嬪簭蹇呴』鍏锋湁鍞竴鐨勫悕绉般備綅浜庝笉鍚屾柟妗堜腑鐨勮Е鍙戠▼搴忓彲浠ュ叿鏈夌浉鍚岀殑鍚嶇О銆
鍦1涓柟妗堜腑锛屾墍鏈夌殑瑙﹀彂绋嬪簭鍚嶇О蹇呴』鏄敮涓鐨勶紝闄や簡璇ヨ姹傚锛屽浜庤兘澶熷垱寤虹殑瑙﹀彂绋嬪簭鐨勭被鍨嬭繕瀛樺湪鍏朵粬闄愬埗銆傚挨鍏舵槸锛屽浜庡叿鏈夌浉鍚岃Е鍙戞椂闂村拰瑙﹀彂浜嬩欢鐨勮〃锛屼笉鑳芥湁2涓Е鍙戠▼搴忋備緥濡傦紝涓嶈兘涓烘煇涓琛ㄥ畾涔2涓BEFORE INSERT瑙﹀彂绋嬪簭鎴2涓AFTER UPDATE瑙﹀彂绋嬪簭銆傝繖鍑犱箮涓嶆槸鏈夋剰涔夌殑闄愬埗锛岃繖鏄洜涓猴紝閫氳繃鍦FOR EACH ROW涔嬪悗浣跨敤BEGIN ... END澶嶅悎璇彞缁撴瀯锛岃兘澶熷畾涔夋墽琛屽鏉¤鍙ョ殑瑙﹀彂绋嬪簭銆傝鍙傝鏈妭鍚庨潰缁欏嚭鐨勭ず渚嬨
姝ゅ锛屾縺娲昏Е鍙戠▼搴忔椂锛屽瑙﹀彂绋嬪簭鎵ц鐨勮鍙ヤ篃瀛樺湪涓浜涢檺鍒讹細
路 瑙﹀彂绋嬪簭涓嶈兘璋冪敤灏嗘暟鎹繑鍥炲鎴风鐨勫瓨鍌ㄧ▼搴忥紝涔熶笉鑳戒娇鐢ㄩ噰鐢CALL璇彞鐨勫姩鎬SQL锛堝厑璁稿瓨鍌ㄧ▼搴忛氳繃鍙傛暟灏嗘暟鎹繑鍥炶Е鍙戠▼搴忥級銆
路 瑙﹀彂绋嬪簭涓嶈兘浣跨敤浠ユ樉寮忔垨闅愬紡鏂瑰紡寮濮嬫垨缁撴潫浜嬪姟鐨勮鍙ワ紝濡START TRANSACTION銆COMMIT鎴ROLLBACK銆
浣跨敤OLD鍜NEW鍏抽敭瀛楋紝鑳藉璁块棶鍙楄Е鍙戠▼搴忓奖鍝嶇殑琛屼腑鐨勫垪锛OLD鍜NEW涓嶅尯鍒嗗ぇ灏忓啓锛夈傚湪INSERT瑙﹀彂绋嬪簭涓紝浠呰兘浣跨敤NEW.col_name锛屾病鏈夋棫琛屻傚湪DELETE瑙﹀彂绋嬪簭涓紝浠呰兘浣跨敤OLD.col_name锛屾病鏈夋柊琛屻傚湪UPDATE瑙﹀彂绋嬪簭涓紝鍙互浣跨敤OLD.col_name鏉ュ紩鐢ㄦ洿鏂板墠鐨勬煇涓琛岀殑鍒楋紝涔熻兘浣跨敤NEW.col_name鏉ュ紩鐢ㄦ洿鏂板悗鐨勮涓殑鍒椼
鐢OLD鍛藉悕鐨勫垪鏄彧璇荤殑銆備綘鍙互寮曠敤瀹冿紝浣嗕笉鑳芥洿鏀瑰畠銆瀵逛簬鐢NEW鍛藉悕鐨勫垪锛屽鏋滃叿鏈SELECT鏉冮檺锛屽彲寮曠敤瀹冦傚湪BEFORE瑙﹀彂绋嬪簭涓紝濡傛灉浣犲叿鏈UPDATE鏉冮檺锛屽彲浣跨敤鈥淪ET NEW.col_name = value鈥鏇存敼瀹冪殑鍊銆傝繖鎰忓懗鐫锛屼綘鍙互浣跨敤瑙﹀彂绋嬪簭鏉ユ洿鏀瑰皢瑕佹彃鍏ュ埌鏂拌涓殑鍊硷紝鎴栫敤浜庢洿鏂拌鐨勫笺
鍦BEFORE瑙﹀彂绋嬪簭涓紝AUTO_INCREMENT鍒楃殑NEW鍊涓0锛屼笉鏄疄闄呮彃鍏ユ柊璁板綍鏃跺皢鑷姩鐢熸垚鐨勫簭鍒楀彿銆
OLD鍜NEW鏄瑙﹀彂绋嬪簭鐨MySQL鎵╁睍銆
閫氳繃浣跨敤BEGIN ... END缁撴瀯锛岃兘澶熷畾涔夋墽琛屽鏉¤鍙ョ殑瑙﹀彂绋嬪簭銆傚湪BEGIN鍧椾腑锛岃繕鑳戒娇鐢ㄥ瓨鍌ㄥ瓙绋嬪簭涓厑璁哥殑鍏朵粬璇硶锛屽鏉′欢鍜屽惊鐜瓑銆備絾鏄紝姝ｅ瀛樺偍瀛愮▼搴忛偅鏍凤紝瀹氫箟鎵ц澶氭潯璇彞鐨勮Е鍙戠▼搴忔椂锛屽鏋滀娇鐢mysql绋嬪簭鏉ヨ緭鍏ヨЕ鍙戠▼搴忥紝闇瑕侀噸鏂板畾涔夎鍙ュ垎闅旂锛屼互渚胯兘澶熷湪瑙﹀彂绋嬪簭瀹氫箟涓娇鐢ㄥ瓧绗︹;鈥濄傚湪涓嬮潰鐨勭ず渚嬩腑锛屾紨绀轰簡杩欎簺瑕佺偣銆傚湪璇ョず渚嬩腑锛屽畾涔変簡1涓UPDATE瑙﹀彂绋嬪簭锛岀敤浜庢鏌ユ洿鏂版瘡涓琛屾椂灏嗕娇鐢ㄧ殑鏂板硷紝骞舵洿鏀瑰硷紝浣夸箣浣嶄簬0锝100鐨勮寖鍥村唴銆傚畠蹇呴』鏄BEFORE瑙﹀彂绋嬪簭锛岃繖鏄洜涓猴紝闇瑕佸湪灏嗗肩敤浜庢洿鏂拌涔嬪墠瀵瑰叾杩涜妫鏌ワ細
mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;
杈冧负绠鍗曠殑鏂规硶鏄紝鍗曠嫭瀹氫箟瀛樺偍绋嬪簭锛岀劧鍚庝娇鐢ㄧ畝鍗曠殑CALL璇彞浠庤Е鍙戠▼搴忚皟鐢ㄥ瓨鍌ㄧ▼搴忋傚鏋滀綘鎵撶畻浠庢暟涓Е鍙戠▼搴忓唴閮ㄨ皟鐢ㄧ浉鍚岀殑瀛愮▼搴忥紝璇ユ柟娉曚篃寰堟湁甯姪銆
鍦ㄨЕ鍙戠▼搴忕殑鎵ц杩囩▼涓紝MySQL澶勭悊閿欒鐨勬柟寮忓涓嬶細
路 濡傛灉BEFORE瑙﹀彂绋嬪簭澶辫触锛屼笉鎵ц鐩稿簲琛屼笂鐨勬搷浣溿
路 浠呭綋BEFORE瑙﹀彂绋嬪簭锛堝鏋滄湁鐨勮瘽锛夊拰琛屾搷浣滃潎宸叉垚鍔熸墽琛岋紝鎵嶆墽琛AFTER瑙﹀彂绋嬪簭銆
路 濡傛灉鍦BEFORE鎴AFTER瑙﹀彂绋嬪簭鐨勬墽琛岃繃绋嬩腑鍑虹幇閿欒锛屽皢瀵艰嚧璋冪敤瑙﹀彂绋嬪簭鐨勬暣涓鍙ョ殑澶辫触銆
路 瀵逛簬浜嬪姟鎬ц〃锛屽鏋滆Е鍙戠▼搴忓け璐ワ紙浠ュ強鐢辨瀵艰嚧鐨勬暣涓鍙ョ殑澶辫触锛夛紝璇ヨ鍙ユ墍鎵ц鐨勬墍鏈夋洿鏀瑰皢鍥炴粴銆傚浜庨潪浜嬪姟鎬ц〃锛屼笉鑳芥墽琛岃繖绫诲洖婊氾紝鍥犺岋紝鍗充娇璇彞澶辫触锛屽け璐ヤ箣鍓嶆墍浣滅殑浠讳綍鏇存敼渚濈劧鏈夋晥銆
杩欐槸MySQL鍙傝冩墜鍐岀殑缈昏瘧鐗堟湰锛屽叧浜嶮ySQL鍙傝冩墜鍐岋紝璇疯闂dev.mysql.com銆傚師濮嬪弬鑰冩墜鍐屼负鑻辨枃鐗堬紝涓庤嫳鏂囩増鍙傝冩墜鍐岀浉姣旓紝鏈炕璇戠増鍙兘涓嶆槸鏈鏂扮殑銆