DDL to disable triggers in Oracle and SQLServer

To disable to trigger that are enabled in cases when loading data manually in a particular schema or table can be done using the following
Oracle:
SELECT 'ALTER TRIGGER ' || trigger_name || ' DISABLE;'
FROM user_triggers
WHERE status = 'ENABLED'

SQL Server:
SELECT 'ALTER TABLE ' + pobj.name + ' DISABLE TRIGGER ' + str.name + ';'
FROM sysobjects str, sysobjects pobj
where str.type = 'TR'
AND pobj.id = str.parent_obj
AND OBJECTPROPERTY(str.[id], 'ExecIsTriggerDisabled') = 0

Leave a Reply