Disable trigger on an update? - 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 2005 Forums

Transact-SQL (2005)

Disable trigger on an update?

Author

Topic

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 15:45:46

OK, in my previous post about an update, I had some questions. Evil DBA asked if I had a trigger on the table which I was trying to update, and I did. When I look at everything again, the reason the update didnt' work is because the trigger code is returning multiple results and causing the error, not the update code.

So my question is now: can you disable a trigger from the update.

Example

If condition1 disable the trigger
else don't disable the trigger

Is this possible?

Thanks for any help.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-16 : 16:04:16

You can do so using boolean logic, but it sounds as if This may not be the best approach for your scenerio.


If 1 = 2
Begin
insert ...
End

Post your trigger and what you are looking to accomplish if you need more assistance.

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 16:10:28

I did the boolean logic and it worked out fine. now I understand how to disable a trigger in code. Thanks for your help!

Kristen
Test

22859 Posts

Posted - 2007-10-16 : 16:10:42

"can you disable a trigger from the update."

yes, but its a scary scenario

evilDBA
Posting Yak Master

155 Posts

Posted - 2007-10-16 : 16:18:08

I have a good intuition :)

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-16 : 16:49:46

Ok, please explain to me in more detail how you can ignore a trigger.

I promise this isn't a homework assignment...

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 16:59:59

Here's what I did in the trigger itself:
/*SELECT value1 and value2

IF (@value1<>@value2)
BEGIN
/*Trigger logic*/

When I did this, if the condition wasn't met the trigger didn't fire, which is what I wanted.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-16 : 17:01:10

I see... I thought there might be some way to do it without changing the trigger or altering anything...

JohnJohn73
Yak Posting Veteran

57 Posts

Posted - 2007-10-16 : 17:05:25

Yeah, Vinnie suggested using boolean logic, so I put the logic in the trigger itself. Worked out pretty good!

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 01:29:58

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

but you don't want to be doing this ... all other processes modifying the table will ALSO have no trigger.

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-17 : 09:36:17

Right. From reading above (using boolean logic) it was looking to me like you could somehow tell the trigger not to fire with your update without having to alter or change anything...and that kinda shocked me. Now I realize that he meant to put the boolean logic inside the trigger which makes more since.

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 12:34:06

"Now I realize that he meant to put the boolean logic inside the trigger which makes more since"

All the trigger can see though is the data in the record being changed ... so you would have to put something in a column in the record to "flag" to the Trigger not to process it ...

Or disable the trigger of course! We do that during bulk import where the trigger wants other data to be present which is coming later ... but never outside that sort of scenario.

Kristen

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-18 : 12:08:40

Using triggers for referential integrity huh?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources