'I want to create a store procedure that will create DDL,DML trigger for a selected table as parameter
Like the below trigger but I need to add update insert and delete all three. for update trigger columns dynamically getting is not able to code
CREATE TRIGGER ddl_trig_createTable
ON ALL SERVER
FOR CREATE_TABLE
AS
DECLARE @databaseName varchar(255)
DECLARE @AffectedTables varchar(255)
SELECT @AffectedTables = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(100)')
IF (@AffectedTables IN ('DynamicallyCreatedTable'))
BEGIN
select @databaseName = CAST(eventdata().query('/EVENT_INSTANCE/DatabaseName[1]/text()') as NVarchar(128))
EXEC('CREATE TRIGGER ' + @databaseName + '.[dbo].[tgrDynamicTableTrigger]
ON
' + @databaseName + '.[dbo].[DynamicallyCreatedTable]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON
-- trigger code here
END')
END
GO
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|