{"id":2431,"date":"2013-01-31T02:53:45","date_gmt":"2013-01-31T02:53:45","guid":{"rendered":"http:\/\/enjoyasp.net\/?p=2431"},"modified":"2015-11-18T10:11:26","modified_gmt":"2015-11-18T10:11:26","slug":"%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e7%89%88%e6%9c%ac%e6%8e%a7%e5%88%b6-ddl%e8%a7%a6%e5%8f%91%e5%99%a8","status":"publish","type":"post","link":"https:\/\/enjoyasp.net\/index.php\/2013\/01\/31\/%e5%ad%98%e5%82%a8%e8%bf%87%e7%a8%8b%e7%89%88%e6%9c%ac%e6%8e%a7%e5%88%b6-ddl%e8%a7%a6%e5%8f%91%e5%99%a8\/","title":{"rendered":"\u5b58\u50a8\u8fc7\u7a0b\u7248\u672c\u63a7\u5236-DDL\u89e6\u53d1\u5668"},"content":{"rendered":"<div>\n\t&#8211;\u53c2\u8003\uff1a<a href=\"http:\/\/www.sqlteam.com\/article\/using-ddl-triggers-in-sql-server-2005-to-capture-schema-changes\">\u5b58\u50a8\u8fc7\u7a0b\u7248\u672c\u63a7\u5236<\/a><\/div>\n<div>\n\t<\/p>\n<pre class=\"brush:sql;first-line:1;pad-line-numbers:true;highlight:null;collapse:false;\">\r\nUSE CedarLog\r\nGO\r\n\r\n\r\nCREATE TABLE [dbo].[ChangeLog](\r\n\t[LogId] [INT] IDENTITY(1,1) NOT NULL,\r\n\t[DatabaseName] [VARCHAR](256) NOT NULL,\r\n\t[EventType] [VARCHAR](50) NOT NULL,\r\n\t[ObjectName] [VARCHAR](256) NOT NULL,\r\n\t[ObjectType] [VARCHAR](25) NOT NULL,\r\n\t[SqlCommand] [NVARCHAR](MAX) NOT NULL,\r\n\t[EventDate] [DATETIME] NOT NULL CONSTRAINT [DF_EventsLog_EventDate]  DEFAULT (GETDATE()),\r\n\t[LoginName] [VARCHAR](256) NOT NULL,\r\n\t[IP] [VARCHAR](50) NOT NULL,\r\n CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED \r\n(\r\n\t[LogId] DESC\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] TEXTIMAGE_ON [PRIMARY]\r\n\r\nGO\r\n\r\nSET ANSI_PADDING OFF\r\nGO\r\n\r\n\r\n\r\nCREATE TRIGGER [backup_objects]\r\nON ALL SERVER \r\nFOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE,\r\nCREATE_TABLE, ALTER_TABLE, DROP_TABLE,CREATE_VIEW, ALTER_VIEW, DROP_VIEW,\r\nCREATE_FUNCTION, ALTER_FUNCTION, DROP_FUNCTION\r\nAS\r\nSET NOCOUNT ON\r\nDECLARE @data XML SET @data = EVENTDATA()\r\nIF @data.value(&#39;(\/EVENT_INSTANCE\/ObjectName)[1]&#39;, &#39;varchar(256)&#39;) NOT LIKE &#39;SqlQuery%&#39;\r\nBEGIN\r\n\tINSERT INTO CedarLog.dbo.changelog(databasename, eventtype, objectname, objecttype, sqlcommand, loginname,IP) \r\n\tVALUES( @data.value(&#39;(\/EVENT_INSTANCE\/DatabaseName)[1]&#39;, &#39;varchar(256)&#39;), \r\n\t@data.value(&#39;(\/EVENT_INSTANCE\/EventType)[1]&#39;, &#39;varchar(50)&#39;), \r\n\t@data.value(&#39;(\/EVENT_INSTANCE\/ObjectName)[1]&#39;, &#39;varchar(256)&#39;), \r\n\t@data.value(&#39;(\/EVENT_INSTANCE\/ObjectType)[1]&#39;, &#39;varchar(25)&#39;), \r\n\t@data.value(&#39;(\/EVENT_INSTANCE\/TSQLCommand)[1]&#39;, &#39;varchar(max)&#39;), \r\n\t@data.value(&#39;(\/EVENT_INSTANCE\/LoginName)[1]&#39;, &#39;varchar(256)&#39;),\r\n\tCONVERT(VARCHAR(50),CONNECTIONPROPERTY(&#39;client_net_address&#39;) ) )\r\nEND\t\r\nGO\r\n\r\nENABLE TRIGGER [backup_objects] ON ALL SERVER\r\nGO\r\n\r\n\r\n\r\n--\u67e5\u770b\u670d\u52a1\u5668\u7ea7\u522b\u7684\u89e6\u53d1\u5668 \r\nSELECT TOP 50 * FROM sys.server_triggers \r\n\r\n--\u67e5\u770b\u670d\u52a1\u5668\u7ea7\u522b\u7684\u89e6\u53d1\u5668\u7684\u5b9a\u4e49 \r\nSELECT * FROM sys.server_sql_modules \r\n\r\n--\u67e5\u770b\u6fc0\u53d1\u89e6\u53d1\u5668\u7684\u6570\u636e\u5e93\u4e8b\u4ef6\u7684\u4fe1\u606f \r\nSELECT TOP 50 * FROM sys.server_trigger_events\r\n\r\n--\u5220\u9664\u670d\u52a1\u5668\u4e0a\u7684DDL\u89e6\u53d1\u5668 \r\nDROP TRIGGER backup_objects ON ALL SERVER\r\n\r\n--\u5931\u6548DDL\u89e6\u53d1\u5668\r\nDISABLE TRIGGER backup_objects ON ALL SERVER\r\n\r\n--\u83b7\u53d6\u6709\u5173\u6570\u636e\u5e93\u8303\u56f4\u5185\u7684\u89e6\u53d1\u5668\u7684\u4fe1\u606f \r\nSELECT * FROM sys.triggers \r\n\r\n--\u83b7\u53d6\u6709\u5173\u6fc0\u53d1\u89e6\u53d1\u5668\u7684\u6570\u636e\u5e93\u4e8b\u4ef6\u7684\u4fe1\u606f \r\nSELECT * FROM sys.trigger_events \r\n\r\n--\u67e5\u770b\u6570\u636e\u5e93\u8303\u56f4\u5185\u7684\u89e6\u53d1\u5668\u7684\u5b9a\u4e49 \r\nSELECT * FROM sys.sql_modules \r\n\r\n--\u5220\u9664\u5f53\u524d\u6570\u636e\u5e93\u4e0a\u7684DDL\u89e6\u53d1\u5668 \r\nDROP TRIGGER backup_objects ON DATABASE\r\n<\/pre>\n<\/div>\n<div>\n\t&nbsp;<\/div>\n","protected":false},"excerpt":{"rendered":"<p>&#8211;\u53c2\u8003\uff1a\u5b58\u50a8\u8fc7\u7a0b\u7248\u672c\u63a7\u5236 USE CedarLog GO CREATE TABLE [dbo].[ [&hellip;]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2431","post","type-post","status-publish","format-standard","hentry","category-sql-server"],"blocksy_meta":[],"_links":{"self":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2431","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/comments?post=2431"}],"version-history":[{"count":0,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/posts\/2431\/revisions"}],"wp:attachment":[{"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/media?parent=2431"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/categories?post=2431"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/enjoyasp.net\/index.php\/wp-json\/wp\/v2\/tags?post=2431"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}