SQL Server Performance Forum – Threads Archive

Create DML trigger inside DDL trigger.

I’m trying to create a trigger (without synamic sql) inside a DDL trigger. Something along the lines of this (so you get the idea, of course it doesn’t work) : ALTER TRIGGER Recreate_trigger
ON DATABASE
FOR CREATE_TABLE
AS
BEGIN DECLARE @data XML
SET @data = EVENTDATA() declare @table varchar(255) set @table = @data.value(‘(/EVENT_INSTANCE/ObjectName)[1]’, ‘nvarchar(255)’) if @table = ‘table_foo’
BEGIN
CREATE TRIGGER [trigger_foo] ON dbo.table_foo
AFTER insert,UPDATE, DELETE
AS
begin set nocount on
— Code end
END END
So whenever the table is dropped I recreate a trigger. Could this be done without using dynamic sql ? I can resolve it using dynamic sql but then the "inside trigger" would be all in red and hard to edit/mantain. Otherwise I could load the recreate script from a file, but I also dislike that option. Any ideas ?

---

I guess dynamic sql is the best option to choose… —
Sunil "Its nice to be Important, But its more important to be Nice"

---

]]>