cannot create trigger on database for DDL_SERVER_LEVEL_EVENTS

%3CLINGO-SUB%20id%3D%22lingo-sub-2631310%22%20slang%3D%22zh-CN%22%3Ecannot%20create%20trigger%20on%20database%20for%20DDL_SERVER_LEVEL_EVENTS%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631310%22%20slang%3D%22zh-CN%22%3E%3CP%3EIs%20this%20bug%3F%20why%20I%20can't%20create%20trigger%26nbsp%3B%20on%20database%20for%20DDL_SERVER_LEVEL_EVENTS%2Cbut%20I%20can%20create%20trigger%20on%20database%20on%26nbsp%3B%26nbsp%3BDDL_DATABASE_LEVEL_EVENTS%EF%BC%8Cwhat's%20the%20reason%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fzh-cn%2Fsql%2Frelational-databases%2Ftriggers%2Fddl-event-groups%3Fview%3Dsql-server-ver15%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ECREATE%20TRIGGER%20log2%0AON%20DATABASE%0AFOR%20DDL_DATABASE_LEVEL_EVENTS%0AAS%0ADECLARE%20%40data%20XML%0ASET%20%40data%20%3D%20EVENTDATA()%0AINSERT%20ddl_log%0A(PostTime%2C%20DB_User%2C%20Event%2C%20TSQL)%0AVALUES%0A(GETDATE()%2C%0ACONVERT(nvarchar(100)%2C%20CURRENT_USER)%2C%0A%40data.value('(%2FEVENT_INSTANCE%2FEventType)%5B1%5D'%2C%20'nvarchar(100)')%2C%0A%40data.value('(%2FEVENT_INSTANCE%2FTSQLCommand)%5B1%5D'%2C%20'nvarchar(2000)')%20)%20%3B%0AGO%0A%0ACREATE%20TRIGGER%20log3%0AON%20DATABASE%0AFOR%20DDL_SERVER_LEVEL_EVENTS%0AAS%0ADECLARE%20%40data%20XML%0ASET%20%40data%20%3D%20EVENTDATA()%0AINSERT%20ddl_log%0A(PostTime%2C%20DB_User%2C%20Event%2C%20TSQL)%0AVALUES%0A(GETDATE()%2C%0ACONVERT(nvarchar(100)%2C%20CURRENT_USER)%2C%0A%40data.value('(%2FEVENT_INSTANCE%2FEventType)%5B1%5D'%2C%20'nvarchar(100)')%2C%0A%40data.value('(%2FEVENT_INSTANCE%2FTSQLCommand)%5B1%5D'%2C%20'nvarchar(2000)')%20)%20%3B%0AGO%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%221.png%22%20style%3D%22width%3A%20699px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302139i41FF31178804CBD4%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%221.png%22%20alt%3D%221.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%222.png%22%20style%3D%22width%3A%20694px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302141iCAFEFB4B600D6E49%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%222.png%22%20alt%3D%222.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%223.png%22%20style%3D%22width%3A%20695px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302140i9388D6438F18EAED%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%223.png%22%20alt%3D%223.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%224.png%22%20style%3D%22width%3A%20686px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302138iA2C47D6B89A481DE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%224.png%22%20alt%3D%224.png%22%20%2F%3E%3C%2FSPAN%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%225.png%22%20style%3D%22width%3A%20682px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302142i1BCE2FCE39C3CED9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%225.png%22%20alt%3D%225.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2631310%22%20slang%3D%22zh-CN%22%3E%3CLINGO-LABEL%3Eerror%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESQL%20Server%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Esql%20server%202019%20develop%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etrigger%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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文件大小的触发器。