DDL Trigger error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

DDL Trigger error

Author

Topic

DBriles
Starting Member

9 Posts

Posted - 2007-06-14 : 11:12:06

Below is a server level ddl trigger that is erroring out. The error is...

Msg 1082, Level 15, State 1, Procedure ddl_trig_login, Line 6
"DROP_SERVER_ROLE_MEMBER" does not support synchronous trigger registration.

Here is the trigger that fails...

if exists (select * from sys.server_triggers where name = 'ddl_trig_login')
drop trigger ddl_trig_login
on all server
GO

create trigger ddl_trig_login
on all server
for DDL_SERVER_SECURITY_EVENTS
as
print 'trigger fired'
GO

This server level trigger works...

if exists (select * from sys.server_triggers where name = 'ddl_trig_login')
drop trigger ddl_trig_login
on all server
GO

create trigger ddl_trig_login
on all server
for DDL_LOGIN_EVENTS
as
print 'trigger fired'
GO

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-14 : 14:11:02

Only ddl_login_events, ddl_gdr_server_events and ddl_authorization_server_events under ddl_server_security_enevts can be used for ddl trigger.

pdav221
Starting Member

4 Posts

Posted - 2008-07-23 : 17:30:39

That's beautiful (not).

Why doesn't Microsoft provide a useful grouping of trigger-supported DDL events in a group name that I can use so I don't need to guess which ones work and which ones do not work?

All I know, is not enough.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources