Disable all triggers on a table, you do what you have to do, after enable only the triggers that have been disabled.
Step 1 : Disable all triggers
1
2--#region Disable triggers
3
4DECLARE @triggers TABLE (SqlEnableTrigger NVARCHAR(MAX));
5DECLARE @SchemaName VARCHAR(128) = 'mySchema'; – ** TO DO **
6DECLARE @TableName VARCHAR(128) = 'mytable'; – ** TO DO **
7DECLARE @SqlTrigger NVARCHAR(MAX);
8
9DELETE FROM @triggers;
10
11INSERT INTO @triggers (SqlEnableTrigger)
12SELECT 'ENABLE TRIGGER ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tg.name) + N' ON ' + QUOTENAME(sc.name) + N'.' + QUOTENAME(tb.name) + N';' AS SqlEnableTrigger
13FROM sys.schemas AS sc
14 INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
15 RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id
16WHERE (sc.name =@SchemaName)
17 AND (tb.name = @TableName)
18 AND (tg.is_disabled = 0);
19
20SET @SqlTrigger = 'DISABLE TRIGGER ALL ON '+QUOTENAME(@SchemaName) + N'.' + QUOTENAME(@TableName);
21PRINT @SqlTrigger;
22EXEC (@SqlTrigger);
23
24--#endregion Disable triggers
Step 2: You do what you have to do.
Your code… to update, to delete, to insert, or everything you want.
Step 3 : Enable all triggers that have been disabled.
1
2--#region Enaable triggers
3
4SET @SqlTrigger = '';
5SELECT @SqlTrigger = @SqlTrigger + SqlEnableTrigger FROM @triggers;
6PRINT @SqlTrigger;
7EXEC (@SqlTrigger);
8
9--#endregion Enaable triggers
List all triggers, the SchemaName is empty when it is a DDL trigger
1
2SELECT ISNULL(sc.name,'') AS SchemaName,
3 ISNULL(tb.name,'(Database)') AS TableName,
4 tg.name,
5 tg.object_id,
6 tg.parent_class,
7 tg.parent_class_desc,
8 tg.parent_id,
9 tg.type,
10 tg.type_desc,
11 tg.create_date,
12 tg.modify_date,
13 tg.is_ms_shipped,
14 tg.is_disabled,
15 tg.is_not_for_replication,
16 tg.is_instead_of_trigger,
17 OBJECTPROPERTY(tg.object_id, 'ExecIsUpdateTrigger') AS isupdate,
18 OBJECTPROPERTY(tg.object_id, 'ExecIsDeleteTrigger') AS isdelete,
19 OBJECTPROPERTY(tg.object_id, 'ExecIsInsertTrigger') AS isinsert,
20 OBJECTPROPERTY(tg.object_id, 'ExecIsAfterTrigger') AS isafter,
21 OBJECTPROPERTY(tg.object_id, 'ExecIsInsteadOfTrigger') AS isinsteadof
22FROM sys.schemas AS sc
23 INNER JOIN sys.tables AS tb ON sc.schema_id = tb.schema_id
24 RIGHT OUTER JOIN sys.triggers AS tg ON tb.object_id = tg.parent_id;
Comments