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
BEGIN DECLARE @data XML
SET @data = EVENTDATA() declare @table varchar(255) set @table = @data.value(‘(/EVENT_INSTANCE/ObjectName)’, ‘nvarchar(255)’) if @table = ‘table_foo’
CREATE TRIGGER [trigger_foo] ON dbo.table_foo
AFTER insert,UPDATE, DELETE
begin set nocount on
— Code 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"