开发者

Trigger at Database Level on DDL Statments

开发者 https://www.devze.com 2023-02-09 21:01 出处:网络
How to create a开发者_如何学运维 database level trigger on DDL statements in Microsoft SQL ?A nice example here

How to create a开发者_如何学运维 database level trigger on DDL statements in Microsoft SQL ?


A nice example here

USE YourDatabase;
GO
CREATE TABLE ddl_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100), TSQL nvarchar(2000));
GO
CREATE TRIGGER log 
ON DATABASE 
FOR DDL_DATABASE_LEVEL_EVENTS 
AS
DECLARE @data XML
SET @data = EVENTDATA()
INSERT ddl_log 
   (PostTime, DB_User, Event, TSQL) 
   VALUES 
   (GETDATE(), 
   CONVERT(nvarchar(100), CURRENT_USER), 
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') ) ;
GO
--Test the trigger
CREATE TABLE TestTable (a int)
DROP TABLE TestTable ;
GO
SELECT * FROM ddl_log ;
GO
0

精彩评论

暂无评论...
验证码 换一张
取 消