cannot create trigger on database for DDL_SERVER_LEVEL_EVENTS

New Contributor

Is this bug? why I can't create trigger  on database for DDL_SERVER_LEVEL_EVENTS,but I can create trigger on database on  DDL_DATABASE_LEVEL_EVENTS,what's the reason ?

 

https://docs.microsoft.com/zh-cn/sql/relational-databases/triggers/ddl-event-groups?view=sql-server-ver15

 

CREATE TRIGGER log2
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

CREATE TRIGGER log3
ON DATABASE
FOR DDL_SERVER_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

 

 

1.png2.png3.png4.png5.png

 

 

1 Reply
按sql server官网上的描述去创建触发器,但是不成功,一直报错。请路过大神赐教。 我想创建一个触发器,用来跟踪更改mdf和ldf文件大小的触发器。