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 |
|
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 |
 |
|
|
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? |
|
|
|
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. |
|
|
|
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). |
|
|
|
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.MyTriggerON dbo.MyTableAFTER UPDATE, DELETEwhat under inserted or deleted that will give me a handle? |
|
|
|
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 |
|
|
|
|
|
|
|
|
can a trigger on table fire off conditionally