Disable Enable Trigger SQL server for a table

sql-server triggers
pang picture pang · Sep 7, 2009 · Viewed 187.2k times · Source

I want to create one proc like below but it has error on syntax. Could anyone pointing out the problem?

Create PROCEDURE [dbo].[my_proc] AS

BEGIN

DISABLE TRIGGER dbo.tr_name ON dbo.table_name

-- some update statement

ENABLE TRIGGER dbo.tr_name  ON dbo.table_name

END

** Error Message : Incorrect syntax near 'ENABLE'.

Answer

Wael Dalloul picture Wael Dalloul · Sep 7, 2009

use the following commands instead:

ALTER TABLE table_name DISABLE TRIGGER tr_name

ALTER TABLE table_name ENABLE TRIGGER tr_name

Related questions

Insert Update trigger how to determine if insert or update

I need to write an Insert, Update Trigger on table A which will delete all rows from table B whose one column (say Desc) has values like the value inserted/updated in the table A's column (say Col1). How would …

Read More
Need to list all triggers in SQL Server database with table name and table's schema

I need to list all triggers in SQL Server database with table name and table's schema. I'm almost there with this: SELECT trigger_name = name, trigger_owner = USER_NAME(uid),table_schema = , table_name = OBJECT_NAME(parent_obj), isupdate = OBJECTPROPERTY( …

Read More
SQL update trigger only when column is modified

By looking at other examples I've come up with the following but it doesn't seem to work as I would like: I want it to only update the modified information if the QtyToRepair value has been updated... but it doesn't …

Read More