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'.
This question is related to
sql-server
triggers
As Mark mentioned, the previous statement should be ended in semi-colon. So you can use:
; DISABLE TRIGGER dbo.tr_name ON dbo.table_name
After the ENABLE TRIGGER OR DISABLE TRIGGER in a new line write GO, Example:
DISABLE TRIGGER dbo.tr_name ON dbo.table_name
GO
-- some update statement
ENABLE TRIGGER dbo.tr_name ON dbo.table_name
GO
The line before needs to end with a ;
because in SQL DISABLE
is not a keyword. For example:
BEGIN
;
DISABLE TRIGGER ...
Below is the Dynamic Script to enable or disable the Triggers.
select 'alter table '+ (select Schema_name(schema_id) from sys.objects o
where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+
Name as EnableScript,*
from sys.triggers t
where is_disabled = 1
if you want to execute ENABLE TRIGGER Directly From Source :
we can't write like this:
Conn.Execute "ENABLE TRIGGER trigger_name ON table_name"
instead, we can write :
Conn.Execute "ALTER TABLE table_name DISABLE TRIGGER trigger_name"
I wanted to share something that helped me out. Idea credit goes to @Siavash and @Shahab Naseer.
I needed something where I could script disable and re enable of triggers for a particular table. I normally try and stay away from tiggers, but sometimes they could be good to use.
I took the script above and added a join to the sysobjects so I could filter by table name. This script will disable a trigger or triggers for a table.
select 'alter table '+ (select Schema_name(schema_id) from sys.objects o
where o.object_id = parent_id) + '.'+object_name(parent_id) + ' ENABLE TRIGGER '+ t.Name as EnableScript,*
from sys.triggers t
INNER JOIN dbo.sysobjects DS ON DS.id = t.parent_id
where is_disabled = 0 AND DS.name = 'tblSubContact'
Below is the simplest way
Try the code
ALTER TRIGGER trigger_name DISABLE
That's it :)
Source: Stackoverflow.com