Forum Discussion

xihaha000's avatar
xihaha000
Copper Contributor
Aug 10, 2021

cannot create trigger on database for DDL_SERVER_LEVEL_EVENTS

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

 

 

 

 

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

Resources