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
 General SQL Server Forums
 New to SQL Server Programming
 can a trigger on table fire off conditionally

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-10-29 : 13:22:06
Hi,

Is there a way to make a sql table trigger fire off conditionally on calling app?

We have this vendor app database which also have in-house custom built app operates on them. Some one are thinking using triggers to audit second part of the app activities, but want to skip the vendor app part of operation for at least performance reason.

To my knowledge, the answer is no. An insert trigger will fire off whenever there is an insert. It doesn't provide a mechanism to check which app/process first, then conditionally fire or not fire.

This is 2008 R2.

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-29 : 13:31:35
A trigger is always fired, but you can conditionally check whether or not to take action.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-10-29 : 13:42:34
quote:
Originally posted by SwePeso

A trigger is always fired, but you can conditionally check whether or not to take action.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA



So even you choose not to take action after confirming the calling source, the firing itself could still have overhead, and a performance hit.

Am I right?
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-29 : 14:50:08
sure, though if you do no selects, deletes or updates while confirming the calling source, the overhead should be immeasurable.
Go to Top of Page

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-10-29 : 18:29:03
Triggers are not fired when using bulk insert, unless you tell it to.

But In your case, I think the best approach is to let the trigger code evaluate, whether or not to take action (like SwePerso and gbritton suggests).
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-10-30 : 16:10:58
Thanks, All!

Now, allow me to extend it one step further.

What is the syntax that will give me the calling schema info?

For instance,

CREATE TRIGGER dbo.MyTrigger
ON dbo.MyTable
AFTER UPDATE, DELETE

what under inserted or deleted that will give me a handle?
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-10-31 : 03:57:54
You can use a combination of @@PROCID to find out the calling routine and then sysobjects or similar to find out the schema.



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
   

- Advertisement -