Geek Answers Handbook

How to make DDL Trigger for a specific table?

I am trying to create a DDL trigger for a specific table, and this is the best I could come up with:

CREATE TRIGGER MyTrigger ON DATABASE FOR DDL_TABLE_EVENTS AS DECLARE @EventData xml SET @EventData=EVENTDATA() IF @EventData.value('(/EVENT_INSTANCE/ObjectType)[1]', 'varchar(50)')='TABLE' AND @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'varchar(50)') ='MyTable' BEGIN --do something special here!! END GO

Is this really the only way to do this? I looked everywhere but could not find the syntax for creating a trigger on a separate table. I think it's really stupid to use xml EVENTDATA ().

+4
source share
2 answers

Yes. This is the way to do it. DDL triggers are associated with the database, not with individual objects. As a result, you cannot subscribe directly to DDL events that occur only on a particular object.

+2
source

DDL DDL_TABLE_EVENTS , such as DDL_TABLE_EVENTS (or, for example, ALTER_TABLE or `DROP_TABLE), are triggered for actions related to categories of objects, and not to specific instances of the object.

Ref. Designing DDL Triggers

+1
source

More articles:


All Articles