{"id":2373,"date":"2014-06-25T16:14:15","date_gmt":"2014-06-25T08:14:15","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=2373"},"modified":"2016-08-04T13:41:56","modified_gmt":"2016-08-04T05:41:56","slug":"%e4%bd%bf%e7%94%a8-trigger-%e7%b4%80%e9%8c%84%e8%b3%87%e6%96%99%e8%a1%a8%e7%9a%84%e6%96%b0%e5%a2%9e%e3%80%81%e4%bf%ae%e6%94%b9%e3%80%81%e5%88%aa%e9%99%a4%e7%9a%84%e8%a1%8c%e7%82%ba","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2014\/06\/25\/%e4%bd%bf%e7%94%a8-trigger-%e7%b4%80%e9%8c%84%e8%b3%87%e6%96%99%e8%a1%a8%e7%9a%84%e6%96%b0%e5%a2%9e%e3%80%81%e4%bf%ae%e6%94%b9%e3%80%81%e5%88%aa%e9%99%a4%e7%9a%84%e8%a1%8c%e7%82%ba\/","title":{"rendered":"\u4f7f\u7528 Trigger \u7d00\u9304\u8cc7\u6599\u8868\u7684\u65b0\u589e\u3001\u4fee\u6539\u3001\u522a\u9664\u7684\u884c\u70ba"},"content":{"rendered":"

\u539f\u6587\uff1a http:\/\/www.dotblogs.com.tw\/jameswu\/archive\/2009\/07\/23\/9643.aspx<\/p>\n

\n

\u5099\u4efd\u5982\u4e0b\uff1a<\/p>\n

\u5e38\u88ab\u8981\u6c42\u7576\u8cc7\u6599\u8868\u5167\u7684\u7d00\u9304\u6709\u88ab\u65b0\u589e\u4fee\u6539\u6216\u522a\u9664\u7684\u6642\u5019\uff0c\u9700\u8981\u540c\u6b65\u7d00\u9304\u6bcf\u6b21\u8cc7\u6599\u8868\u8b8a\u52d5\u7684\u524d\u5f8c\u8cc7\u6599\uff0c\u9019\u6642\u5019\u6211\u5011\u53ef\u4ee5\u5229\u7528 Trigger \u4fbf\u53ef\u505a\u5230\u7b26\u5408\u9019\u500b\u529f\u80fd\u7684\u9700\u6c42\uff0c\u800c\u4e14\u53ef\u4ee5\u9069\u7528\u5230\u5927\u90e8\u4efd\u7684\u4f7f\u7528\u60c5\u5883\uff0c\u53c8\u56e0\u70ba Trigger \u672c\u8eab\u4e5f\u53ef\u4ee5\u6355\u6349\u5230\u8cc7\u6599\u5eab\u7684 DML \u7684\u4e8b\u4ef6\uff0c\u5982 : INSERT\u3001UPDATE \u3001DELETE \uff0c\u6211\u5011\u4e5f\u80fd\u6355\u6349\u5728\u8a72\u8cc7\u6599\u5217\u88ab\u4fee\u6539\u4e4b\u5f8c (AFTER DML) \u624d\u4f86\u57f7\u884c Trigger \uff0c\u6240\u4ee5\u525b\u597d\u6eff\u8db3\u6211\u5011\u8981\u8a18\u9304\u7684\u8cc7\u6599\u8868\u8b8a\u52d5\u7684\u884c\u70ba\u3002<\/p>\n

Microsoft SQL Server \u7684 Trigger \u5167\u6703\u5305\u542b\u6709\u5169\u500b\u7279\u6b8a\u7684\u865b\u64ec\u8cc7\u6599\u8868 inserted \u8207 deleted ; \u9019\u5169\u500b\u8cc7\u6599\u8868\u5206\u5225\u6703\u64f7\u53d6\u5f15\u767c Trigger \u7684\u8cc7\u6599\u5217\u4fee\u6539\u4e4b\u524d\u3001\u5f8c\u8cc7\u6599\u5167\u5bb9\uff0c\u7576\u7136\u5b83\u5011\u6703\u5132\u5b58\u55ae\u7b46\u6216\u591a\u7b46\u8cc7\u6599\u5217\u66f4\u65b0\u7684\u8cc7\u6599\uff0c\u4e0b\u8868\u8aaa\u660e\u4e86\u6bcf\u500b DML \u7684\u64cd\u4f5c\u5167\u7684 inserted\u3001deleted \u6703\u5305\u542b\u7684\u8cc7\u6599\u5217\u4e4b\u5167\u5bb9\u3002<\/p>\n\n\n\n\n\n\n
\u8cc7\u6599\u5eab DML<\/em><\/strong><\/td>\ninserted<\/em><\/strong><\/td>\ndeleted<\/em><\/strong><\/td>\n<\/tr>\n
INSERT<\/td>\n\u63d2\u5165\u7684\u65b0\u8cc7\u6599\u5167\u5bb9<\/td>\n<\/td>\n<\/tr>\n
UPDATE<\/td>\n\u6b32\u66f4\u65b0\u7684\u65b0\u8cc7\u6599\u5167\u5bb9<\/td>\n\u66f4\u65b0\u524d\u7684\u820a\u8cc7\u6599\u5167\u5bb9<\/td>\n<\/tr>\n
DELETE<\/td>\n<\/td>\n\u88ab\u522a\u9664\u524d\u7684\u8cc7\u6599\u5167\u5bb9<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

\u7576\u6709\u65b0\u7684\u8cc7\u6599\u5217\u63d2\u5165\u8a72\u8cc7\u6599\u8868\u6642\uff0c\u6703\u89f8\u767c\u00a0 INSERT \u4e8b\u4ef6\uff0c\u6703\u8907\u88fd\u8a72\u65b0\u589e\u8cc7\u6599\u5217\u7684\u5167\u5bb9\u5230\u865b\u64ec\u8cc7\u6599\u8868 inserted \uff0c\u4f46\u4e0d\u6703\u5b58\u5728\u6709\u865b\u64ec\u8cc7\u6599\u8868 deleted ;
\n\u82e5\u662f\u67d0\u7b46\u8cc7\u6599\u5217\u88ab\u66f4\u65b0\u4e86\uff0c\u90a3\u9ebc\u6703\u89f8\u767c UPDATE \u4e8b\u4ef6\uff0c\u4e26\u4e14\u6703\u8907\u88fd\u6b32\u66f4\u65b0\u7684\u65b0\u8cc7\u6599\u5167\u5bb9\u5230\u865b\u64ec\u8cc7\u6599\u8868 inserted \uff0c\u540c\u6642\u5c07\u820a\u8cc7\u6599\u5217\u5167\u5bb9\u8907\u88fd\u5230\u865b\u64ec\u8cc7\u6599\u8868 deleted ;
\n\u522a\u9664\u67d0\u7b46\u8cc7\u6599\u5217\u7684\u8a71\uff0c\u5c07\u89f8\u767c DELETE \u4e8b\u4ef6\uff0c\u6b64\u6642\u6703\u5c07\u522a\u9664\u524d\u7684\u8cc7\u6599\u5217\u5167\u5bb9\u8907\u88fd\u5230\u865b\u64ec\u8cc7\u6599\u8868 deleted \uff0c\u56e0\u70ba\u6c92\u6709\u8cc7\u6599\u65b0\u589e\u6216\u66f4\u65b0\uff0c\u6240\u4ee5\uff0c\u4e0d\u6703\u5b58\u5728\u6709\u865b\u64ec\u8cc7\u6599\u8868 inserted \u3002<\/p>\n

\u5728\u5f8c\u9762\u9644\u4e0a\u7684\u9762\u7684 Trigger \u5167\u5bb9\u4e2d\u7684 6~14 \u884c\uff0c\u6211\u4fbf\u5229\u7528\u4e0a\u8ff0\u7684\u539f\u7406\u4f86\u5224\u65b7\u89f8\u767c\u6b64 Trigger \u7684 DML \u4e8b\u4ef6\u70ba\u4f55? \u6240\u4ee5\uff0c\u6211\u5011\u9700\u8981\u5c0d\u65bc\u8cc7\u6599\u5eab\u7684\u904b\u4f5c\u539f\u7406\u662f\u6709\u9032\u4e00\u6b65\u4e86\u89e3\uff0c\u624d\u6709\u8fa6\u6cd5\u9748\u6d3b\u904b\u7528\u7684\u3002<\/p>\n

\u4e0a\u9762\u7684\u4e00\u5806\u8cc7\u6599\u5eab\u7684\u904b\u4f5c\u539f\u7406\uff0c\u4e3b\u8981\u662f\u6211\u60f3\u5728\u540c\u4e00\u500b Trigger \u4e2d\u540c\u6642\u4f7f\u7528\u591a\u500b AFTER DML \u4e8b\u4ef6\uff0c\u56e0\u70ba\u6211\u4e0d\u60f3\u7dad\u8b77\u540c\u4e00\u985e\u7684 Trigger \u5167\u5bb9\uff0c\u7576\u7136\uff0c\u4e5f\u5f97\u770b\u4f7f\u7528\u7684\u60c5\u5883\uff0c\u5982\u679c\u4f60\u7684 AFTER INSERT \u9084\u5305\u542b\u5176\u4ed6\u7684\u904b\u7b97\u908f\u8f2f\uff0c\u90a3\u5efa\u8b70\u9084\u662f\u5c07\u5167\u5bb9\u5206\u958b\u5169\u4efd Trigger \u6bd4\u8f03\u6070\u7576\u3002<\/p>\n

\u518d\u8005\uff0c\u4e4b\u524d\u770b\u904e\u5f88\u591a\u7bc4\u4f8b (\u5305\u542b\u6211\u4ee5\u524d\u81ea\u5df1\u5beb\u7684) \uff0c\u82e5\u8981\u7d00\u9304\u6240\u6709\u7684\u6b77\u53f2\u7d00\u9304\u5927\u90e8\u5206\u662f\u65b0\u589e\u8ddf\u539f\u59cb\u8cc7\u6599\u8868\u76f8\u540c\u7684\u6b04\u4f4d (Schema) \uff0c\u9802\u591a\u5728\u5f8c\u9762\u65b0\u589e\u4e00\u500b\u8cc7\u6599\u6b04\u4f4d\u4f86\u8a18\u9304\u8a72\u7b46\u662f\uff0cupdate\u3001insert \u6216 delete\uff0c\u9019\u6a23\u96d6\u7136\u53ef\u884c\uff0c\u4f46\u6211\u60f3\u5230\u842c\u4e00\u539f\u59cb\u8cc7\u6599\u8868\u7684\u6b04\u4f4d\u6709\u65b0\u589e\u3001\u4fee\u6539\u6216\u522a\u9664\uff0c\u90a3\u9ebc\u9019\u500b\u6b77\u53f2\u8cc7\u6599\u8868\u4e5f\u8981\u540c\u6b65\u66f4\u65b0\uff0c\u800c\u6211\u5011\u53c8\u6709\u53ef\u80fd\u5fd8\u4e86\u540c\u6b65\u66f4\u65b0\uff0c\u800c\u9020\u6210\u67d0\u4e9b\u6b04\u4f4d\u7684\u8cc7\u6599\u5167\u5bb9\u6c92\u6709\u88ab\u8a18\u9304\u5230\uff0c\u6211\u8003\u91cf\u5230\u9019\u500b\u554f\u984c\uff0c\u4e14\u60f3\u63d0\u9ad8\u6b64 Trigger \u7684\u91cd\u8907\u5229\u7528\u6027\uff0c\u56e0\u6b64\uff0c\u6211\u60f3\u5c07\u8a72\u8cc7\u6599\u5217\u5167\u7684\u8cc7\u6599\u5167\u5bb9\u5168\u90e8\u5b58\u6210 XML \u8cc7\u6599\u683c\u5f0f\uff0c\u5c07\u6b64 XML \u8cc7\u6599\u653e\u5728\u4e00\u500b XML \u8cc7\u6599\u578b\u614b\u7684\u6b04\u4f4d\u5373\u53ef\uff0c\u7576\u7136\u9019\u6709\u500b\u7f3a\u9ede\u662f\uff0c\u7576\u8981\u8abf\u95b1\u67d0\u7b46\u6b77\u53f2\u8cc7\u6599\u6642\uff0c\u4f60\u53ef\u80fd\u6c92\u8fa6\u6cd5\u5f88\u7c21\u55ae\u7684\u5c31\u67e5\u8a62\u5230\u8a72\u7b46\u8a18\u9304\u7684\u5167\u5bb9\uff0c\u53ef\u80fd\u9700\u8981\u518d\u4f7f\u7528\u5176\u4ed6\u65b9\u6cd5\u4f86\u89e3\u6790\u51fa XML \u7684\u5167\u5bb9\u624d\u884c\uff0c\u9019\u90e8\u5206\u5728\u7a0d\u5f8c\u6211\u4e5f\u6703\u63d0\u4f9b\u4e00\u500b\u65b9\u6cd5\u6280\u5de7\u4f86\u505a\u67e5\u8a62\u7684\u3002<\/p>\n

\u8981\u5c07\u8cc7\u6599\u5217\u8f49\u63db\u6210 XML \u683c\u5f0f\u7684\u8cc7\u6599\u5176\u5be6\u4e0d\u96e3\uff0c\u53ea\u8981\u5229\u7528 SQL Server 2000 \u4ee5\u5f8c\u63d0\u4f9b\u7684\u00a0 FOR XML \uff0c\u4fbf\u53ef\u8f15\u6613\u7684\u5c07\u8cc7\u6599\u8868\u8cc7\u6599\u8f49\u63db\u6210\u9700\u8981\u7684\u4efb\u4f55 XML \u5167\u5bb9\u683c\u5f0f\u4e86 \uff0c\u5728\u4e0b\u65b9\u7684 Trigger \u5167\u5bb9\u4e2d\u7684 17 \u8207 20 \u884c\u4fbf\u6709\u4f7f\u7528\u5230\u6b64\u529f\u80fd\u3002<\/p>\n

\u4f46\u70ba\u4e86\u5c0d XML \u5167\u5bb9\u7684\u67e5\u8a62\u65b9\u4fbf\uff0c\u6211\u9078\u64c7\u4e86\u4f7f\u7528 FOR XML RAW, ELEMENTS, ROOT \u4f86\u7522\u51fa\u6211\u8981\u7684\u683c\u5f0f\u3002<\/p>\n

\u4e0b\u65b9\u662f\u6211\u5be6\u4f5c\u7684\u4e00\u500b\u7bc4\u4f8b\uff0c\u9996\u5148\u8981\u5efa\u7acb\u4e00\u500b\u65b0\u7684 LOG \u8cc7\u6599\u8868\u4f86\u5b58\u653e\u7d00\u9304 :<\/p>\n

CREATE TABLE [dbo].[LOG](\r\n     [id] [uniqueidentifier] NOT NULL,\r\n     [dbname] [varchar](25) NULL,\r\n     [recoder] [xml] NULL,\r\n     [istype] [tinyint] NULL,\r\n     [updatedate] [datetime] NULL,\r\n  CONSTRAINT [PK_LOG] PRIMARY KEY CLUSTERED([id] ASC)\r\n  WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]\r\n ) ON [PRIMARY]\r\n GO\r\n ALTER TABLE [dbo].[LOG] ADD  CONSTRAINT [DF_LOG_id]  DEFAULT (newid()) FOR [id]\r\n GO\r\n ALTER TABLE [dbo].[LOG] ADD  CONSTRAINT [DF_LOG_updatedate]  DEFAULT (getdate()) FOR [updatedate]\r\n GO<\/pre>\n
\u518d\u4f86\u6211\u5011\u4f7f\u7528\u5728 NorthWind \u8cc7\u6599\u5eab\u7684 Customers \u8cc7\u6599\u8868\u5167\u5efa\u7acb\u4e00\u500b\u65b0\u7684 Trigger \uff0c\u6b64 Trigger \u6703\u5728 Customers \u8cc7\u6599\u8868\u6709\u88ab\u4fee\u6539\u5f8c\u89f8\u767c :<\/p>\n
CREATE TRIGGER [dbo].[trCustomers_UPDATE_INSERT_DELETE] ON [dbo].[Customers] AFTER UPDATE,INSERT,DELETE\r\nAS\r\nBEGIN\r\n   DECLARE @record XML\r\n   DECLARE @IsType TINYINT\r\n   SET @IsType=''\r\n   IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)\r\n       SET @IsType = 1    --Insert\r\n\r\n   IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)\r\n       SET @IsType = 2    --Update\r\n\r\n   IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)\r\n       SET @IsType = 3    --Delete\r\n\r\n   --\u53ea\u60f3\u8a18\u9304 UPDATE \u4e8b\u4ef6\u66f4\u65b0\u8cc7\u6599\u524d\u7684\u820a\u8cc7\u6599\r\n   IF (@IsType = 1)\r\n       SET @record=(SELECT * FROM inserted FOR XML RAW('Customers'), ELEMENTS,ROOT)\r\n   ELSE\r\n       SET @record=(SELECT * FROM deleted FOR XML RAW('Customers'), ELEMENTS,ROOT)\r\n\r\n   IF (@IsType <>'')\r\n   BEGIN\r\n       INSERT INTO [log]([dbname],[recoder],[istype])VALUES('Customers',@record  ,@IsType)\r\n   END\r\nEND<\/pre>\n
\u4e0a\u9762\u7684\u5c31\u5df2\u7d93\u505a\u5230\u6211\u5011\u8981\u7684\u5132\u5b58\u4fee\u6539\u7d00\u9304\u7684\u529f\u80fd\u4e86\uff0c\u4e0d\u904e\u7f8e\u4e2d\u4e0d\u8db3\u7684\u662f\u9019\u6a23\u7684\u8cc7\u6599\u7d50\u69cb\u4e0d\u662f\u90a3\u9ebc\u7684\u65b9\u4fbf\u67e5\u8a62\uff0c\u5fc5\u9808\u8981\u518d\u5229\u7528 OPENXML \u5c07 XML \u8cc7\u6599\u53d6\u51fa\u4f86\u3002<\/p>\n
\u5047\u8a2d\u8981\u8ffd\u8e64\u67d0\u4e00\u7b46\u8a18\u9304\uff0c\u4f60\u53ef\u4ee5\u5148\u4f7f\u7528 SQL \u6307\u4ee4\u67e5\u8a62\u5230\u8a72\u7b46\u8a18\u9304\u5f8c\uff0c\u5c07 Log \u8cc7\u6599\u8868\u7684 recoder \u6b04\u4f4d\u7684 XML \u8cc7\u6599\u4e1f\u7d66 OPENXML \u8655\u7406\u5373\u53ef\uff0c\u5982\u4e0b\u65b9\u6cd5 : (\u5176\u5be6\u9019\u65b9\u6cd5\u53ef\u4ee5\u61c9\u7528\u5728 Master and Detail view \u60c5\u5883\u4e2d, \u4e5f\u5c31\u662f\u4f60\u5c07\u4e4b\u524d\u7684\u7d00\u9304\u5148\u4ee5\u985e\u4f3c GridView \u7684\u5217\u8868\u986f\u793a\u6240\u6709\u7d00\u9304\u5f8c, \u518d\u4f7f\u7528 DetailsView \u7b49\u5c07\u8a73\u7d30\u7684\u7d00\u9304\u986f\u793a\u51fa\u4f86)<\/p>\n
DECLARE @xmldoc XML\r\nSELECT @xmldoc = recoder FROM Log WHERE [id]='AF35C6FF-E36E-41D5-9158-911931ABDB19'\r\n\r\nDECLARE @hDoc AS INT\r\nEXEC sp_xml_preparedocument @hDoc OUTPUT, @xmldoc\r\nSELECT *\r\nFROM OPENXML(@hDoc, '\/root\/Customers', 2)\r\nWITH ([CustomerID] [nchar](5),\r\n    [CompanyName] [nvarchar](40),\r\n    [ContactName] [nvarchar](30),\r\n    [ContactTitle] [nvarchar](30),\r\n    [Address] [nvarchar](60),\r\n    [City] [nvarchar](15),\r\n    [Region] [nvarchar](15),\r\n    [PostalCode] [nvarchar](10),\r\n    [Country] [nvarchar](15),\r\n    [Phone] [nvarchar](24),\r\n    [Fax] [nvarchar](24))\r\n\r\nEXEC sp_xml_removedocument @hDoc<\/pre>\n","protected":false},"excerpt":{"rendered":"
\u539f\u6587\uff1a http:\/\/www.dotblogs.com.tw\/jameswu\/archive\/2009\/07\/ … \u95b1\u8b80\u5168\u6587 \u4f7f\u7528 Trigger \u7d00\u9304\u8cc7\u6599\u8868\u7684\u65b0\u589e\u3001\u4fee\u6539\u3001\u522a\u9664\u7684\u884c\u70ba<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[47],"tags":[],"class_list":["post-2373","post","type-post","status-publish","format-standard","hentry","category-sql-server","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=2373"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/2373\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=2373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=2373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=2373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}