views:

193

answers:

2

I'm 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 it? I looked everywhere but couldn't find a syntax to create the trigger on a spceific table. I think it is really silly needing to use the xml EVENTDATA().

+1  A: 

Yes. It's the way to do it. DDL triggers are associated with the database rather than individual objects. As a consequence, you can't subscribe directly to DDL events happening only on a specific object.

Mehrdad Afshari
+1  A: 

DDL triggers such as DDL_TABLE_EVENTS (or such as ALTER_TABLE or `DROP_TABLE) fire for actions relating to object categories, rather than specific instances of an object.

Ref. Designing DDL Triggers

Mitch Wheat