SQL触发器

SQL触发器 邢 SQL瑙，彂鍣? 銆?銆?瑙，彂鍣ㄦ槸涓?绉嶇壒娈婄被鍨嬬殑瀛樺偍杩囩?锛屼笉鐢辩敤鎴风洿鎺ヨ皟鐢ㄣ?傚垱寤鸿Е ?銆?銆?CREATE PROCEDURE 鎴?CREATE TRIGGER Е ?銆?銆?鐢ㄨЕ鍙戝櫒杩樺彲浠ュ己鍒舵 ?銆?銆?Microsoft SQL Server? 2000 鎻愪緵浜嗕袱绉嶄富瑕佹満鍒舵潵 э細绾,潫鍜岃Е鍙戝櫒銆傝Е 鐢熸晥銆傚敜閱掕皟鐢ㄨЕ鍙戝櫒浠ュ搷搴?INSERT銆乁PDATE 鎴?DELETE Е鍙 Transact-SQL ? 銆?銆?瑙，彂鍣ㄥ彲閫氳繃鏁版嵁搴撲腑鐨勭浉鍏宠〃瀹炵幇绾ц仈鏇存敼锛涗笉杩囷紝閫氳繃绾ц仈寮曠敤瀹屾暣鎬х害鏉熷彲浠ユ洿鏈夋晥鍦版墽琛岃繖浜涙洿鏀广??銆?銆?瑙，彂鍣ㄥ彲浠ュ己鍒舵瘮鐢?CHECK ?銆?銆?涓?CHECK 绾,潫涓嶅悓锛岃Е SELECT 姣旇緝鎻掑叆鎴栨洿鏂扮殑鏁版嵁锛屼互 ?銆?銆彇瀵圭瓥銆?INSERT銆乁PDATE 鎴?DELETE?銆?銆?姣旇緝瑙，彂鍣ㄤ笌绾,潫 銆?銆?绾,潫鍜岃Е鍙戝櫒鍦ㄧ壒娈婃儏鍐典笅鍚勬湁浼樺娍銆傝Е鐢?Transact-SQL 浠,浣嗗畠鍦ㄦ墍缁欏嚭鐨勫姛鑳戒笂骞朵笉鎬绘槸鏈?濂界殑鏂规硶銆?銆?銆?瀹炰綋瀹屾暣鎬ф?PRIMARY KEY 鍜?UNIQUE 惧姛鑳藉彲浠ユ弧瓒冲簲鐢ㄧ?搴忕殑鍔熻兘闇?姹傦紝鍩熷畬鏁存?у簲閫氳繃 CHECK 绾 ?(RI) 鍒欏簲閫氳繃 FOREIGN KEY 寮哄埗銆? 眰鏃讹紝瑙，彂鍣ㄥ氨鏋佷负鏈夌敤銆備緥濡傦細 銆?銆?闄ら潪 REFERENCES 瀛愬彞瀹氫箟浜嗙骇鑱斿紩鐢ㄦ搷浣滐紝鍚，垯 FOREIGN KEY 煎畬鍏ㄥ尮閰嶇殑鍊兼潵楠岃瘉鍒楀?笺??銆?銆?CHECK Е鍙戝櫒銆? 蹇呴』浣跨敤瑙，彂鍣ㄣ??銆?銆?瑙，彂鍣ㄥ彲閫氳繃鏁版嵁搴撲腑鐨勭浉鍏宠〃瀹炵幇绾ц仈鏇存敼锛涗笉杩囷紝閫氳繃绾ц仈寮曠敤瀹屾暣鎬х害鏉熷彲浠ユ洿鏈夋晥鍦版墽琛岃繖浜涙洿鏀广??х殑鏇存敼 慨鏀广?傚綋鏇存敼澶栭敭涓旀柊鍊间笌涓婚敭涓嶅 Е 鍦?titleauthor.title_id Е鍙戝櫒锛屼娇瀹冨湪鏂板?间笌 titles.title_id 浣跨敤 FOREIGN KEY ?銆?銆?濡傛灉瑙，彂鍣ㄨ〃涓婂瓨鍦ㄧ害鏉燂紝鍒欏湪 INSTEAD OF 瑙，彂鍣ㄦ墽琛屽悗浣嗗湪 AFTER 瑙，彂鍣ㄦ墽琛屽墠妫?鏌ヨ繖浜涚害?INSTEAD OF 瑙，彂鍣ㄦ搷浣滃苟涓斾笉鎵ц AFTER 瑙，彂鍣ㄣ?? SQL? 銆?銆?CREATE TRIGGER trigger_name 銆?銆?ON { table | view } 銆?銆?[ WITH ENCRYPTION ] 銆?銆?{ 銆?銆?{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ DELETE ] [ UPDATE ] } 銆?銆?[ WITH APPEND ] 銆?銆?[ NOT FOR REPLICATION ] 銆?銆?AS 銆?銆?[ { IF UPDATE ( column ) 銆?銆?[ { AND | OR } UPDATE ( column ) ] 銆?銆?[ ...n ] 銆?銆?| IF ( COLUMNS_UPDATED ( ) updated_bitmask ) 銆?銆?column_bitmask [ ...n ] 銆?銆?} ] 銆?銆?sql_statement [ ...n ] 銆?銆?} 銆?銆?} 銆?銆?鍙傛暟 銆?銆?trigger_name Е鍙戝櫒鐨勫悕绉般?傝Е鍙戝櫒鍚嶇О蹇呴』绗，悎鏍囪瘑绗，鏈夎?呭悕绉般??銆?銆?Table | view ц 浘锛屾湁鏃剁О涓鸿Е 瑙嗗浘鐨勬墍鏈夎?呭悕绉般??銆?銆?WITH ENCRYPTION 銆?銆?鍔犲瘑 syscomments 琛ㄤ腑鍖呭惈 CREATE TRIGGER ?WITH ENCRYPTION 瑙，彂鍣ㄤ綔涓?SQL Server 澶嶅埗鐨勪竴閮ㄥ垎鍙戝竷銆?銆?銆?AFTER 銆?銆?鎸囧畾瑙，彂鍣ㄥ彧鏈夊湪瑙，彂 SQL 鎵嶆縺鍙戙?傛墍鏈夌殑寮曠敤绾ц 鎵嶈兘鎵цЕ鍙戝櫒銆?銆?銆?濡傛灉浠呮寚瀹?FOR 鍏抽敭瀛楋紝鍒?AFTER ? AFTER 瑙，彂鍣ㄣ??銆?銆?INSTEAD OF 銆?銆?鎸囧畾鎵цЕ鍙?SQL Е鍙ョ殑鎿嶄綔銆??INSERT銆乁PDATE 鎴?DELETE 彞鏈?澶氬彲浠ュ畾涔変竴涓?INSTEAD OF 鏈?INSTEAD OF ?銆?銆?INSTEAD OF 瑙，彂鍣ㄤ笉鑳藉湪 WITH CHECK OPTION 鎸囧畾浜?WITH CHECK OPTION 閫夐」鐨勫彲鏇存柊瑙嗗浘娣诲姞 INSTEAD OF SQL Server 灏嗕骇鐢 ALTER VIEW 鍒犻櫎璇ラ?夐」鍚庢墠鑳藉畾涔?INSTEAD OF 瑙，彂鍣ㄣ??銆?銆?{ [DELETE] [,] [INSERT] [,] [UPDATE] } Е 杩欎簺閫夐」銆?銆?銆?瀵逛簬 INSTEAD OF ON DELETE 绾ц仈鎿嶄綔寮曠敤鍏崇郴鐨勮〃涓婁娇鐢?DELETE 鏈?ON UPDATE 绾ц仈鎿嶄綔寮曠敤鍏崇郴鐨勮〃涓婁娇鐢?UPDATE 閫夐」銆?銆?銆?WITH APPEND Е 绾у埆鏄?65 槸 70 鎴栨洿楂橈紝鍒欎笉蹇呬娇鐢?WITH APPEND 瀛愬彞娣诲姞鐜版湁绫诲瀷鐨勫叾瀹冭Е 70 鎴栨洿楂樼殑 CREATE TRIGGER 鐨勯粯璁よ ?sp_dbcmptlevel銆?銆?銆?WITH APPEND 涓嶈 兘涓?INSTEAD OF AFTER 瑙，彂 ?FOR 鏃讹紙娌?湁 INSTEAD OF 鎴?AFTER锛夛紝鎵嶈兘浣跨敤 WITH APPEND銆備互鍚庣殑鐗堟湰灏嗕笉 WITH APPEND 鍜?FORВ閲婁负 AFTER锛夈??銆?銆?NOT FOR REPLICATION Е鍙戝櫒鎵?娑夊強鐨勮〃鏃讹紝涓嶅簲鎵цЕ鍙戝櫒銆?銆?銆?AS Е鍙戝櫒瑕佹墽琛岀殑鎿嶄綔銆?銆?銆?sql_statement Е鍙戝櫒鐨勬潯浠跺拰鎿嶄綔銆傝Е鍙戝櫒鏉′欢鎸囧畾鍏跺畠鍑嗗垯锛屼互纭 DELETE銆両NSERT 鎴?UPDATE ц?銆?銆?褰撳皾璇?DELETE銆両NSERT 鎴?UPDATE 鎿嶄綔鏃讹紝Transact-SQL瑙，彂鍣ㄦ搷浣滃皢鐢熸晥銆? 鐨?Transact-SQL Е Е Transact-SQL 甯稿父鍖呭惈鎺уREATE TRIGGER 細 銆?銆?* deleted 鍜?inserted 绫讳技浜庡畾涔夎Е ?deleted 銆?銆?SELECT * 銆?銆?FROM deleted 銆?銆?* у埆绛変簬 70锛岄偅涔堝湪 DELETE銆両NSERT 鎴?UPDATE 瑙，彂鍣ㄤ腑锛孲QL Server inserted 鍜?deleted 琛ㄤ腑鐨?text銆乶text 鎴?image ?inserted 鍜?deleted 琛ㄤ腑鐨?text銆乶text 鍜?image 鍊笺?傝嫢瑕佸湪 INSERT 鎴?UPDATE 瑙，彂鍣ㄤ腑妫?绱?柊鍊硷紝璇峰皢 inserted у埆鏄?65 鎴栨洿浣庢椂锛 inserted 鎴?deleted text銆乶text 鎴?image 鍒楋紝灏 ?銆? 80 鎴栨洿楂樻椂锛孲QL Server 繃 INSTEAD OF 瑙，彂鍣ㄦ洿鏂?text銆乶text 鎴?image 鍒椼??銆?銆?n 鸿Е?Transact-SQL ?IF UPDATE (column) UPDATE (column) 瀛愬彞鍖呭惈澶氬垪銆?銆?銆?IF UPDATE (column) ?INSERT 鎴?UPDATE 鎿嶄綔锛屼笉鑳界敤浜?DELETE ON 氫簡琛ㄥ悕锛屾墍浠ュ湪 IF UPDATE ?INSERT 鎴?UPDATE 悗鎸囧畾鍗曠嫭鐨?UPDATE(column) 瀛愬彞銆傚湪 INSERT 鎿嶄綔涓?IF UPDATE 灏嗚繑鍥?TRUE 鍊硷紝鍥犱负杩欎簺鍒楁彃鍏ヤ簡鏄惧紡鍊兼垨闅愭??(NULL) 鍊笺??銆?銆?璇存槑 IF UPDATE (column) 瀛愬彞鐨勫姛鑳界瓑鍚屼簬 IF銆両F...ELSE 鎴?WHILE 屽苟涓斿彲浠ヤ娇鐢?BEGIN...END ?Е UPDATE (column)銆?銆?銆?column INSERT 鎴?UPDATE 浠ユ槸 SQL Server ?銆?銆?IF (COLUMNS_UPDATED()) INSERT 鎴?UPDATE 瑙，彂鍣ㄤ腑銆侰OLUMNS_UPDATED 杩斿洖 varbinary 浣嶆ā寮忥紝琛ㄧず鎻掑叆鎴栨洿鏂颁簡琛ㄤ腑鐨勫摢浜涘垪銆?銆?銆?COLUMNS_UPDATED 鍑芥暟浠ヤ粠宸，埌鍙崇殑椤哄簭杩斿洖浣嶏紝鏈?宸，竟鐨勪负鏈?涓嶉噸瑕佺殑浣嶃?傛渶宸，竟鐨勪 Е鍙戝櫒鍖呭惈 8 鍒椾互涓婏紝鍒?COLUMNS_UPDATED 杩斿洖澶氫釜瀛楄妭锛屾渶宸，竟鐨勪负鏈?涓嶉噸瑕佺殑瀛楄妭銆傚湪 INSERT 鎿嶄綔涓?COLUMNS_UPDATED TRUE 鍊硷紝鍥犱负杩欎簺鍒楁彃鍏ヤ簡鏄惧紡鍊兼垨闅愭??(NULL) 鍊笺??Е 敤 COLUMNS_UPDATED銆?銆?銆?bitwise_operator ?銆?銆?updated_bitmask 垨鎻掑叆鐨勫垪銆備緥濡傦紝琛?t1 鍖呭惈鍒?C1銆丆2銆丆3銆丆4 鍜?C5銆傚亣瀹氳〃 t1 涓婃湁 UPDATE C2銆丆3 鍜?C4 芥湁鏇存柊锛屾寚瀹氬??14 C2 鏈夋洿鏂帮紝鎸囧畾鍊?2銆?銆?銆?comparison_operator ?(=) 妫?鏌?updated_bitmask ぇ浜庡彿 (>) 妫?鏌?updated_bitmask 存柊銆?銆?銆?column_bitmask 妫?鏌ユ槸鍚，凡鏇存柊鎴栨彃鍏ヤ簡杩欎簺鍒椼??銆?銆?娉ㄩ噴 銆?銆?瑙，彂鍣ㄥ父甯哥 с?係QL Server ALTER TABLE 鍜?CREATE TABLE锛夋彁渚涘,鏄庡紩鐢ㄥ畬鏁存??(DRI)锛涗絾鏄?DRI 涓嶆 с?傝嫢瑕佸己鍒跺紩鐢ㄥ畬鏁存?э紙鏈夊叧琛ㄧ殑LTER TABLE 鍜?CREATE TABLE 鐨?PRIMARY KEY 鍜?FOREIGN KEY Е鍙戝櫒琛ㄥ瓨鍦ㄧ害鏉燂紝鍒欏湪 INSTEAD OF 瑙，彂鍣ㄦ墽琛屼箣鍚庡拰 AFTER 瑙，彂 INSTEAD OF 瑙，彂鍣ㄦ搷浣滀笖涓嶆墽琛岋紙婵?鍙戯級AFTER 瑙，彂鍣ㄣ?? sp_settriggerorder AFTER 瑙，彂鍣 ?INSERT銆乁PDATE 鍜?DELETE AFTER 湁鍏跺畠 AFTER Е鍙戝櫒灏嗕互闅忔満椤哄簭鎵ц?銆?銆?濡傛灉 ALTER TRIGGER Е鍙戝櫒锛屽垯灏嗛櫎 э紝鑰屼笖蹇呴』鐢?sp_settriggerorder 閲嶇疆鎺掑簭鍊笺??Е鍙?SQL 闄ょ殑瀵硅薄鍏宠仈鐨勫紩鐢ㄧ骇鑱旀搷浣滃拰绾,潫妫?鏌ワ級鎴愬姛鎵цAFTER 瑙，彂鍣ㄦ墠浼氭墽琛屻?侫FTER Е ?UPDATE 鍜?DELETE 寮曠敤绾ц仈鎿嶄綔鐨勬晥鏋溿??銆?銆?瑙，彂鍣ㄩ檺鍒?銆?銆?CREATE TRIGGER ?銆?銆?瑙，彂鍣ㄥ鐨? 紝璇蜂互鐩稿悓鐨勬柟寮忛檺瀹氳〃鍚嶃??銆?銆?鍦ㄥ悓涓?鏉?CREATE TRIGGER ?INSERT 鍜?UPDATE锛夊畾涔夌浉鍚岀殑瑙，彂鍣ㄦ搷浣溿?? DELETE/UPDATE 鎿嶄綔涓婂畾涔変簡绾ц INSTEAD OF DELETE/UPDATE 瑙，彂鍣ㄣ??銆?銆?鍦ㄨЕ鍙戝櫒鍐呭彲浠ユ寚瀹氫换鎰忕殑 SET ?SET 閫夐」鍦ㄨЕ鍙戝櫒鎵цЕ鍙戝櫒鎵ц?銆?銆?涓庝娇鐢ㄥ瓨鍌ㄨ繃绋嬩竴鏍凤紝褰撹Е鍙戝櫒婵?鍙戞椂锛屽皢鍚戣皟鐢ㄥ簲鐢ㄧ?搴忚 繑鍥炵粨鏋溿?傝嫢瑕侀伩鍏嶇敱浜庤Е鍙戝櫒婵?鍙戣?屽悜搴旂敤绋嬪簭杩斿洖缁撴灉锛 ?SELECT Е ?SELECT Е 诲啓鍏ュ厑璁镐慨鏀硅Е ?SET NOCOUNT 彞浠ラ伩鍏嶈繑鍥炰换浣曠粨鏋滈泦銆?銆?銆?DELETE 瑙，彂鍣ㄤ笉鑳芥崟鑾?TRUNCATE TABLE ?TRUNCATE TABLE WHERE 瀛愬彞鐨?DELETE鑳芥墽琛岃Е鍙戝櫒銆傚洜涓?TRUNCATE TABLE TRUNCATE TABLE DELETE ? RITETEXT Е鍙戝櫒銆?銆?銆?瑙，彂鍣ㄤ腑涓嶅厑璁镐互涓?Transact-SQL ?銆?銆?ALTER DATABASE CREATE DATABASE DISK INIT 銆?銆?DISK RESIZE DROP DATABASE LOAD DATABASE 銆?銆?LOAD LOG RECONFIGURE RESTORE DATABASE 銆?銆?RESTORE LOG 銆?銆?璇存槑 鐢变簬 SQL Server Е鍙戝櫒銆?銆?銆?澶氫釜瑙，彂鍣?銆?銆?SQL Server 讹紙DELETE銆両NSERT 鎴?UPDATE锛夊垱寤Е鍙戝櫒銆備緥濡傦紝濡傛灉瀵瑰凡鏈?UPDATE 瑙，彂鍣ㄧ殑琛ㄦ墽琛?CREATE TRIGGER FOR UPDATE锛屽垯灏嗗垱寤哄彟涓?涓 Е 欢锛圛NSERT銆乁PDATE 鎴?DELETEЕ鍙戝櫒銆?銆?銆?璇存槑 濡傛灉瑙，彂鍣ㄥ悕绉颁笉鍚岋紝鍒?CREATE TRIGGER 70 ЕЕЕ鍙戝櫒鍚嶇О鐩稿悓锛屽垯 SQL Server у埆绛変簬鎴栧皬浜?65锛屽垯浣跨敤 CREATE TRIGGER 竴绫诲瀷鐨勪换浣曠幇鏈夎Е鍙戝櫒锛屽嵆浣胯Е鍙戝櫒鍚嶇О涓嶅悓銆傛湁鍏虫洿澶氫俊 ?sp_dbcmptlevel銆?銆?銆?閫掑綊瑙，彂鍣?銆?銆?褰撳湪 sp_dboption ?recursive triggers 璁剧疆鏃讹紝SQL Server 杩樺厑璁歌Е鍙戝櫒鐨勯?掑綊璋冪敤銆?銆?銆?閫掑綊瑙，彂鍣ㄥ厑璁稿彂鐢熶袱绉嶇被鍨嬬殑閫掑綊锛?銆?銆?* 闂存帴閫掑綊 銆?銆?* 鐩存帴閫掑綊 銆?銆?浣跨敤闂存帴閫掑綊鏃讹紝搴旂敤绋嬪簭鏇存柊琛?T1锛屼粠鑰屾縺鍙戣Е鍙戝櫒 TR1 T2 瑙，彂鍣?T2 灏嗘縺鍙戝苟鏇存柊 T1銆?銆?銆?浣跨敤鐩存帴閫掑綊鏃讹紝搴旂敤绋嬪簭鏇存柊琛?T1锛屼粠鑰屾縺鍙戣Е鍙戝櫒 TR1 T1銆傜敱浜庤〃 T1 ?TR1 ?娇鐢ㄤ簡闂存帴瑙，彂鍣ㄩ?掑綊锛屽張浣跨敤浜嗙洿鎺ヨЕ鍙戝櫒閫掑綊銆傚亣瀹氬湪琛?T1 涓 ?TR1 鍜?TR2銆傝Е鍙戝櫒 TR1 閫掑綊鍦版洿鏂拌〃 T1銆俇PDATE ?TR1 鍜?TR2 鍚勬墽琛屼竴娆??傝??TR1 鐨勬墽琛屽皢瑙，彂 TR1锛堥?掑綊锛夊拰 TR2 鐨勬墽琛屻?傜粰瀹氳Е鍙戝櫒鐨?inserted 鍜?deleted 琛ㄥ彧鍖呭惈涓庡敜閱掕皟鐢ㄨЕ鍙戝櫒鐨?UPDATE ?銆?銆?璇存槑 sp_dboption 鐨?recursive triggers Е Е?銆?銆?绂佺敤 recursive triggers sp_configure 灏?nested triggers 鏈嶅姟鍣ㄩ?夐」璁剧疆涓?0銆?銆?銆?濡傛灉浠讳竴瑙，彂鍣ㄦ墽琛屼簡 ROLLBACK TRANSACTION ф槸澶氬皯锛岄兘涓嶄細杩涗竴姝ユ墽琛屽叾瀹冭Е鍙戝櫒銆??銆?銆?瑙，彂鍣ㄦ渶澶氬彲浠ュ祵濂?32 ЕЕ鍙戝櫒鐨勮〃锛屽垯绗 Е鍙戝櫒灏嗘縺娲伙紝鐒跺悗璇ヨЕ Е чЕЕ鍙戝 ?sp_configure 灏?nested triggers 閫夐」璁剧疆涓?0 ЕЕ ?sp_dboption 鐨?recursive triggers 璁剧疆鏃犲叧銆?銆?銆?寤惰繜鍚嶇О瑙,瀽 銆?銆?SQL Server Transact-SQL 瀛樺偍杩囩?銆佽Е鍙戝櫒鍜屾壒澶勭悊寮曠敤缂栬瘧鏃朵笉瀛樺湪鐨勮〃銆傝繖绉嶈兘鍔涚О涓哄欢杩熷悕绉拌В鏋愩 Transact-SQL 瀛樺偍杩囩?銆佽Е鍙戝櫒鎴栨壒澶勭悊寮曠敤鍦ㄥ瓨ц sp_dbcmptlevel 璁剧疆锛夌瓑浜?65 В鏋愬拰缂栬瘧銆?銆?銆?鏉冮檺 銆?銆?CREATE TRIGGER 彂鍣ㄧ殑琛ㄦ墍鏈夎?呫?乻ysadmin ?db_owner 鍜?db_ddladmin ? SELECT 鏈?INSERT銆丏ELETE 鍜?UPDATE ??INSTEAD OF INSERT銆丏ELETE 鍜?UPDATE 鐗规潈锛 INSERT銆丏ELETE 鍜?UPDATE ц?銆?銆?绀轰緥 銆?銆?A. 浣跨敤甯,湁鎻愰啋娑堟伅鐨勮Е鍙戝櫒 銆?銆?褰撴湁浜鸿瘯鍥惧湪 titles 琛ㄤ腑娣诲姞鎴栨洿鏀规暟 ?銆?銆?璇存槑 娑堟伅 50009 鏄?sysmessages sp_addmessage銆?銆?銆?USE pubs 銆?銆?IF EXISTS (SELECT name FROM sysobjects 銆?銆?WHERE name = 'reminder' AND type = 'TR') 銆?銆?DROP TRIGGER reminder 銆?銆?GO 銆?銆?CREATE TRIGGER reminder 銆?銆?ON titles 銆?銆?FOR INSERT, UPDATE 銆?銆?AS RAISERROR (50009, 16, 10) 銆?銆?GO 銆?銆?B. 浣跨敤甯,湁鎻愰啋鐢 Е鍙戝櫒 銆?銆?褰?titles 佺粰鎸囧畾鐨勪汉鍛?(MaryM)銆?銆?銆?USE pubs 銆?銆?IF EXISTS (SELECT name FROM sysobjects 銆?銆?WHERE name = 'reminder' AND type = 'TR') 銆?銆?DROP TRIGGER reminder 銆?銆?GO 銆?銆?CREATE TRIGGER reminder 銆?銆?ON titles 銆?銆?FOR INSERT, UPDATE, DELETE 銆?銆?AS 銆?銆?EXEC master..xp_sendmail 'MaryM', 銆?銆?'Don''t forget to print a report for the distributors.' 銆?銆?GO 銆?銆?C. 鍦?employee 鍜?jobs 琛ㄤ箣闂翠娇鐢ㄨЕ鍙戝櫒涓氬姟瑙勫垯 銆?銆?鐢变簬 CHECK 敤瀹氫箟浜嗗垪绾ф 繀椤诲畾涔変负瑙，彂鍣ㄣ??Е鍙戝櫒锛屽綋鎻掑叆鎴栨洿鏂伴泧鍛樺伐浣滅骇鍒?(job_lvls) 鏃讹紝璇ヨЕ鍙戝櫒妫?鏌ユ寚瀹氶泧鍛樼殑宸ヤ綔绾у 勮寖鍥村唴銆傝嫢瑕佽幏寰楅?傚綋鐨勮寖鍥达紝蹇呴』寮曠敤 jobs 琛ㄣ??銆?銆?USE pubs 銆?銆?IF EXISTS (SELECT name FROM sysobjects 銆?銆?WHERE name = 'employee_insupd' AND type = 'TR') 銆?銆?DROP TRIGGER employee_insupd 銆?銆?GO 銆?銆?CREATE TRIGGER employee_insupd 銆?銆?ON employee 銆?銆?FOR INSERT, UPDATE 銆?銆?AS 銆?銆?/* Get the range of level for this job type from the jobs table. */ 銆?銆?DECLARE @min_lvl tinyint, 銆?銆?@max_lvl tinyint, 銆?銆?@emp_lvl tinyint, 銆?銆?@job_id smallint 銆?銆?SELECT @min_lvl = min_lvl, 銆?銆?@max_lvl = max_lvl, 銆?銆?@emp_lvl = i.job_lvl, 銆?銆?@job_id = i.job_id 銆?銆?FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 銆?銆?JOIN jobs j ON j.job_id = i.job_id 銆?銆?IF (@job_id = 1) and (@emp_lvl <> 10) 銆?銆?BEGIN 銆?銆?RAISERROR ('Job id 1 expects the default level of 10.', 16, 1) 銆?銆?ROLLBACK TRANSACTION 銆?銆?END 銆?銆?ELSE 銆?銆?IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl) 銆?銆?BEGIN 銆?銆?RAISERROR ('The level for job_id:%d should be between %d and %d.', 銆?銆?16, 1, @job_id, @min_lvl, @max_lvl) 銆?銆?ROLLBACK TRANSACTION 銆?銆?END 銆?銆?D. 浣跨敤寤惰繜鍚嶇О瑙,瀽 銆?銆?涓 嬩緥鍒涘缓涓や釜瑙，彂鍣ㄤ互璇存槑寤惰繜鍚嶇О瑙,瀽銆?銆?銆?USE pubs 銆?銆?IF EXISTS (SELECT name FROM sysobjects 銆?銆?WHERE name = 'trig1' AND type = 'TR') 銆 ?銆?DROP TRIGGER trig1 銆?銆?GO 銆?銆?-- Creating a trigger on a nonexistent table. 銆?銆?CREATE TRIGGER trig1 銆?銆?on authors 銆?銆?FOR INSERT, UPDATE, DELETE 銆? 銆?AS 銆?銆?SELECT a.au_lname, a.au_fname, x.info 銆?銆?FROM authors a INNER JOIN does_not_exist x 銆?銆?ON a.au_id = x.au_id 銆?銆?GO 銆?銆?-- Here is the statement to actually see the text of the trigger. 銆?銆?SELECT o.id, c.text 銆?銆?FROM sysobjects o INNER JOIN syscomments c 銆?銆?ON o.id = c.id 銆?銆?WHERE o.type = 'TR' and o.name = 'trig1' 銆?銆?-- Creating a trigger on an existing table, but with a nonexistent 銆?銆?-- column. 銆?銆?USE pubs 銆?銆?IF EXISTS (SELECT name FROM sysobjects 銆?銆?WHERE name = 'trig2' AND type = 'TR') 銆?銆?DROP TRIGGER trig2 銆 ?銆?GO 銆?銆?CREATE TRIGGER trig2 銆?銆?ON authors 銆?銆?FOR INSERT, UPDATE 銆? 銆?AS 銆?銆?DECLARE @fax varchar(12) 銆?銆?SELECT @fax = phone 銆?銆?FROM authors 銆?銆?GO 銆?銆?-- Here is the statement to actually see the text of the trigger. 銆 ?銆?SELECT o.id, c.text 銆?銆?FROM sysobjects o INNER JOIN syscomments c 銆?銆?ON o.id = c.id 銆?銆?WHERE o.type = 'TR' and o.name = 'trig2' 銆?銆?E. 浣跨敤 COLUMNS_UPDATED ?employeeData 琛ㄥ拰涓? 涓?auditEmployeeData 琛ㄣ?備汉鍔涜祫婧愰儴鐨勬垚鍛樺彲浠ヤ慨鏀?employeeData ?彿鐮?(SSN) auditEmployeeData 瀹?牳琛ㄣ??銆?銆?閫氳繃浣跨敤 COLUMNS_UPDATED() 鍔熻兘锛屽彲 8 鍒楁墍鍋氱殑鏇存敼鏃讹紝COLUMNS_UPDATED() 鎵嶈 捣浣滅敤銆?銆?銆?USE pubs 銆?銆?IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 銆?銆?WHERE TABLE_NAME = 'employeeData') 銆?銆?DROP TABLE employeeData 銆?銆?IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES 銆?銆?WHERE TABLE_NAME = 'auditEmployeeData') 銆?銆? DROP TABLE auditEmployeeData 銆?銆?GO 銆?銆?CREATE TABLE employeeData ( 銆?銆? emp_id int NOT NULL, 銆?銆?emp_bankAccountNumber char (10) NOT NULL, 銆?銆? emp_salary int NOT NULL, 銆?銆?emp_SSN char (11) NOT NULL, 銆?銆?emp_lname nchar (32) NOT NULL, 銆?銆?emp_fname nchar (32) NOT NULL, 銆?銆?emp_manager int NOT NULL 銆? 銆?) 銆?銆?GO 銆?銆?CREATE TABLE auditEmployeeData ( 銆?銆?audit_log_id uniqueidentifier DEFAULT NEWID(), 銆?銆?audit_log_type char (3) NOT NULL, 銆?銆? audit_emp_id int NOT NULL, 銆?銆?audit_emp_bankAccountNumber char (10) NULL, 銆? 銆?audit_emp_salary int NULL, 銆?銆?audit_emp_SSN char (11) NULL, 銆?銆?audit_user sysname DEFAULT SUSER_SNAME(), 銆?銆?audit_changed datetime DEFAULT GETDATE() 銆? 銆?) 銆?銆?GO 銆?銆?CREATE TRIGGER updEmployeeData 銆?銆?ON employeeData 銆?銆? FOR update AS 銆?銆?/*Check whether columns 2, 3 or 4 has been updated. If any or all of columns 2, 3 or 4 have been changed, create an audit record. The bitmask is: power(2,(2-1))+power(2,(3-1))+power(2,(4-1)) = 14. To check if all columns 2, 3, and 4 are updated, use = 14 in place of >0 (below).*/ 銆?銆?IF (COLUMNS_UPDATED() & 14) > 0 銆?銆?/*Use IF (COLUMNS_UPDATED() & 14) = 14 to see if all of columns 2, 3, and 4 are updated.*/ 銆?銆?BEGIN 銆?銆?-- Audit OLD record. 銆?銆?INSERT INTO auditEmployeeData 銆?銆?(audit_log_type, 銆?銆?audit_emp_id, 銆?銆? audit_emp_bankAccountNumber, 銆?銆?audit_emp_salary, 銆?銆?audit_emp_SSN) 銆?銆? SELECT 'OLD', 銆?銆?del.emp_id, 銆?銆?del.emp_bankAccountNumber, 銆?銆? del.emp_salary, 銆?銆?del.emp_SSN 銆?銆?FROM deleted del 銆?銆?-- Audit NEW record. 銆?銆?INSERT INTO auditEmployeeData 銆?銆?(audit_log_type, 銆?銆?audit_emp_id, 銆 ?銆?audit_emp_bankAccountNumber, 銆?銆?audit_emp_salary, 銆?銆?audit_emp_SSN) 銆 ?銆?SELECT 'NEW', 銆?銆?ins.emp_id, 銆?銆?ins.emp_bankAccountNumber, 銆?銆? ins.emp_salary, 銆?銆?ins.emp_SSN 銆?銆?FROM inserted ins 銆?銆?END 銆?銆?GO 銆? 銆?/*Inserting a new employee does not cause the UPDATE trigger to fire.*/ 銆?銆 ?INSERT INTO employeeData 銆?銆?VALUES ( 101, 'USA-987-01', 23000, 'R-M53550M', N'Mendel', N'Roland', 32) 銆?銆?GO 銆?銆?/*Updating the employee record for employee number 101 to change the salary to 51000 causes the UPDATE trigger to fire and an audit trail to be produced.*/ 銆?銆?UPDATE employeeData 銆?銆?SET emp_salary = 51000 銆?銆?WHERE emp_id = 101 銆?銆?GO 銆?銆?SELECT * FROM auditEmployeeData 銆 ?銆?GO 銆?銆?/*Updating the employee record for employee number 101 to change both the bank account number and social security number (SSN) causes the UPDATE trigger to fire and an audit trail to be produced.*/ 銆?銆?UPDATE employeeData 銆?銆?SET emp_bankAccountNumber = '133146A0', emp_SSN = 'R-M53550M' 銆?銆?WHERE emp_id = 101 銆?銆?GO 銆?銆?SELECT * FROM auditEmployeeData 銆?銆?GO 銆?銆?F. 浣跨敤 COLUMNS_UPDATED 娴嬭瘯 8 鍒椾互涓? 8 鍒 椾互澶栫殑鍒楃殑鏇存柊鏃讹紝蹇呴』浣跨敤 UBSTRING 鍑芥暟娴嬭瘯鐢?COLUMNS_UPDATED 杩斿洖鐨勯?傚綋鐨勪綅銆備笅渚嬫祴璇曞奖鍝?Northwind.dbo.Customers 3 5 9 鍒楃殑鏇存柊銆?銆?銆?USE Northwind 銆?銆?DROP TRIGGER tr1 銆? 銆?GO 銆?銆?CREATE TRIGGER tr1 ON Customers 銆?銆?FOR UPDATE AS 銆?銆?IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1)) 銆?銆?+ power(2,(5-1))) 銆?銆 ?AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1))) 銆?銆?) 銆?銆?PRINT 'Columns 3, 5 and 9 updated' 銆?銆?GO 銆?銆?UPDATE Customers 銆?銆?SET ContactName=ContactName, 銆?銆?Address=Address, 銆?銆?Country=Country 銆?銆?GO