bypassing trigger - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

bypassing trigger

Author

Topic

yaronkl67
Starting Member

7 Posts

Posted - 2007-01-12 : 04:37:20

Is there a way to bypass/skip an update trigger?
For example, I have an update trigger on table MyTable.
When I call this query:
update MyTable set x = x +1
I don't want the trigger to be activated.

Thank you.

Kristen
Test

22859 Posts

Posted - 2007-01-12 : 04:40:59

ALTER TABLE dbo.MyTable DISABLE TRIGGER ALL
...
ALTER TABLE dbo.MyTable ENABLE TRIGGER ALL

but you are disabling it for ALL activity on that table, not just your Update

Kristen

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 04:47:50

Please note that disabling trigger does not stop the logging overhead associated with the trigger.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-12 : 04:59:14

"logging overhead"

Sounds worth knowing Harsh; can you explain what that is please? Thanks

Kristen

yaronkl67
Starting Member

7 Posts

Posted - 2007-01-12 : 05:06:41

thank you guys.
I don't suppose there is a way to disable the trigger just for this specific command?

yaronkl67
Starting Member

7 Posts

Posted - 2007-01-12 : 05:32:55

OK, another question on the same subject:
What is the best way to check if a trigger is disabled? if found that if I disable a trigger, then sysobjects.status changes a flag (0x800 is cleared), but i am reluctact to rely on that.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 05:46:27

Kristen:

Check this nice article by Tony Rogerson:
[url]http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/10/27/1248.aspx[/url]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-01-12 : 05:50:19

yaronkl67:

To know whether the trigger is disabled or not, you can use OBJECTPROPERTY function:

SELECT OBJECTPROPERTY(OBJECT_ID('Trigger1'), 'ExecIsTriggerDisabled')

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Kristen
Test

22859 Posts

Posted - 2007-01-12 : 06:16:38

Thanks for that Harsh. Turns out its worse than I realised, given that INSERTED and DELETED are materialised from the TLog, which in turn means moving the disk heads back to re-read the file

I might rethink some of my Triggers on that basis ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources