Override trigger 1

[zrefugee]

Does anyone know if a sql statment can be executed in SQL Server 2000 and not execute a trigger set up for a particular table. I need to override the trigger to do an insert statement in a table for an application but need to leave it in place for other situations. Any help would be greatly appreciated.

 

[jaylou]

ALTER TABLE TableName DISABLE TRIGGER NoTax

do your stuff here

ALTER TABLE TableName ENABLE TRIGGER NoTax

 

[jbenson001]

jaylou's example will work, but what if your trigger is disabled at a time when the "other" situation occurs, it won't fire then.

 

[zrefugee]

The trigger not firing if a another part of this system needs it could definitely be a problem. I really need a way just to disable the trigger only for a particular sql transaction.

 

[DrSql]

Depending on your trigger..
Majority of the time when you insert, delete, update coulmn the trigger gets activiated.
Here is an example.


[COLOR=red yellow]
CREATE trigger dbo.table_i on dbo.table
for insert
as
declare @data varchar(255)
declare cur_time1 cursor for
select columnA
from inserted
open cur_time1
readnext:
fetch next from cur_time1 into
@data
if @@FETCH_STATUS <> 0
goto eof
/* You can do any scenerios below*/
/* You can change "not like" to (=, >,<,<>)*/
if @data not like 'Scenerio_That_You_Dont_Want_ToUpdate%'
Begin
exec stored_procdurre
End

goto readnext
eof:
close cur_time1
deallocate cur_time1

RETURN
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
[/color]



If you need any expalnation let me know.

Dr. Sql
goEdeveloper@yahoo.com
Good Luck.