Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
vk59
Starting Member
38 Posts |
 Posted - 2004-02-03 : 10:38:52
|
| I have a trigger for insert,update and delete on a SQL Server table. I would like to disable the trigger when I execute insert,update or delete statements from Query Analyser but trigger should be fired when i insert,update or delete from Enterprise Manager or through front end application.Please help me.Thanks in advance.Vinnu` |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-03 : 11:07:54
|
| Before you execute the query in query analyser you could put a value into context_info in sysprocesses and check for that in the trigger.You could check program_name from sysprocesses in the triggerOther than checking values I don't think there is any way of selectively disabling the trigger.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
vk59
Starting Member
38 Posts |
Posted - 2004-02-04 : 09:02:10
|
Hi NR, Thanks for prompt reply.I am new to TSQL programming.Regarding context_info ,its storing binary data.How and what value do i need to put in context_info column .Could you please post me code sinppet if any?ThanksVinnuquote:
Originally posted by nr
Before you execute the query in query analyser you could put a value into context_info in sysprocesses and check for that in the trigger.You could check program_name from sysprocesses in the triggerOther than checking values I don't think there is any way of selectively disabling the trigger.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
` |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-02-04 : 09:05:54
|
| Actually, this should work nicely:CREATE TRIGGER myTrigger ON myTable FOR UPDATE ASIF APP_NAME() LIKE '%query analyzer%' RETURN... rest of trigger code goes hereIt doesn't disable the trigger, but it will detect if Query Analyzer is being used and will exit the trigger if it is. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-04 : 09:09:57
|
| I'm guessing that's the program_name from sysprocesses.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
vk59
Starting Member
38 Posts |
Posted - 2004-02-04 : 10:00:18
|
Great ..Its working fine..Thanks robvolk and NRVinnuquote:
Originally posted by robvolk
Actually, this should work nicely:CREATE TRIGGER myTrigger ON myTable FOR UPDATE ASIF APP_NAME() LIKE '%query analyzer%' RETURN... rest of trigger code goes hereIt doesn't disable the trigger, but it will detect if Query Analyzer is being used and will exit the trigger if it is.
` |
|
|
|
|
|
|
Disable Triggers