After Update Trigger --- Question
-
Ladies / Gentlemen:
I have been away from using MSSQL Server for many years and I am just getting back to using it again and I have the following question on Setting up an After Update Trigger.
How do I prevent the Trigger or part of the Trigger from executing for certain programs?
-
You can find the program name of the session from this:
Select program_name from master..sysprocesses where spid = @@spid
You can run the trigger code conditionally on what that returns. However, it is not good practice to use the system tables in this way, because there is no guarantee this will be portable to future versions.
If you have different UPDATE functionality required for different clients, you should use 2 different stored procs and have the client programs execute the stored proc rather than a direct SQL UPDATE statement.
-
PW
Thank you for your reply, but why would you want to leave this for programmers to have to remember to do. I thought that that is why triggers were created in the first place?
Mark Moss
-
>>why would you want to leave this for programmers to have to remember to do.
Well, someone, somewhere has to rememebr
What happens when a new application comes along that either does or doesn't need specific trigger functionality ? What happens when an application is upgraded and changes its name in its connection string ? Who remembers the trigger even exists, nevermind remembers to dig into the code to find the list of program names that execute certain code blocks ?
-
Firstly, as someone pointed out, don't use system tables... But you can use the well documented APP_NAME() function as in
select app_name() or if app_name() = 'sfdsaf', etc
Also, if your apps can be in groups of some sort, then you could have an applications table. Each trigger does some check against this table to see if the application (which you can determine using app_name) should execute this trigger or not.
You could even have a table like
ApplicationName TriggerName Allowed --------------- ----------- ------- App1 Trigger1 0 App2 Trigger1 1 App1 Trigger2 1 App2 Trigger2 1
This would at least make things centralised and harder to "forget"
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply