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

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s