How to make trigger code execute only for a specific application?

Question

I was wondering if it is possible to enable a trigger just for my current application and not for any sql executed against the table.

The situation:

There are two applications working on the same database. App1 and App2.

I have a trigger on 'MyTable' which should only be fired when App1 is executing a query, but not when App2 is doing so.

• Super Peon: Well its logical . isn't it ? where would you have the app name ? If you do have access to it in the trigger ( inserted ) then its easy .
• Nick Chammas: Patric, as an alternative to using APP_NAME(), you can also use SET CONTEXT_INFO. SET CONTEXT_INFO is commonly used to send info to triggers like this.

Answer 1

While not a good design, it is doable. You'll need to put logic in the trigger so it causes the code to only be executed when the correct application is connected.

Create trigger... 
As
 If app_name() = 'something' 
 begin 
 put code here 
 end

• Aaron Bertrand: It may not be a concern in this specific case, but other potential readers should note that app_name() is very easy to spoof via connection string properties.
• db2: Yup, very easy to spoof. Don't do this for security purposes, but for working around minor software deficiencies, or other convenient tricks, you're probably okay. We've got a few that do this.
• mrdenny: Very true, this would be VERY easy to get around. But assuming that there's no naught intentions of the application users (I can't believe I just put that) it'll work. Not the best idea, but it'll do the job. Jon, thanks for formatting. Wasn't going to try doing that on my phone.