March 28, 2011 at 1:14 pm
Running SQL 2005.
There is a table named CO in an ERP package that we purchased that stores Customer Order Header information.
Our staff has created a trigger on the CO table that fires on insert or update to a Customer Order. One of this trigger's purposes is to return a message to an Order Clerk if the customer has a Customer Order Hold Code set to True and it prevents the order from being created or updated.
Unfortunately, there is a job that users can run that will update the CO table if there are certain errors. When it encounters an order for a customer that is on a Customer Order Hold, it causes this process to fail.
I would like to have this trigger bypass the Customer Order Hold Code check ONLY if the update to the order is occurring due to this error processing program. This job can run at any time of day so to disable this trigger would not be our answer.
Is there any way to know what program initiated the Customer Order update, allowing me to ignore the Customer Order Hold Code check if the error processing program is attempting to update a Customer Order?
I have tried SELECT APP_NAME() but this always returns the same name from our ERP package. If it is run from SQL SSMS, it returns 'Microsoft SQL Server Management Studio - Query'.
Any help would be greatly appreciated.
Thanks!
Mike
March 28, 2011 at 6:35 pm
You could monitor it in SQL Server Profiler and filter the results down to just updates on your CO table. That should give you an idea of what users are modifying that table and where they are modifying it from.
March 29, 2011 at 5:51 am
One of the problems with triggers is that they're difficult to control. Based on what you've just outlined, what about having two different processes for updating the data and one of the processes passes the message along at the appropriate moment. Then you don't have to mess with the trigger at all.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 9:25 am
My goal is to know which program is updating a particular row in the table as it occurs. If the update is caused by a user updating from the normal Customer Order maintenance program(s), then execute a certain part of the trigger that will notify the user of the customer being on Customer Order Hold.
If the update to a particular row is being processed by our Error Processing program, then ignore the Customer Order Hold code validation.
March 29, 2011 at 9:29 am
On a previous platform I had worked on, the trigger programs were passed information from the system that the update for the row was being performed. We knew what job, user and program had initiated the change. I was hoping that kind of information would be available in SQL as well.
If that type of information were available, my change becomes very simple. If it is not available, the change is much more complex.
March 29, 2011 at 10:41 am
You can get that information, if you have both individual logins and your connection strings include the app that is connecting. If you're using a service login for the applications, then you can't know which individual is connecting unless you pass that into the procedure as a parameter.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 29, 2011 at 11:04 am
We do have individual logins for every user. However, we have dozens of users who do Customer Order maintenance. Some of these same users also run the Customer Order Error Processing routine so knowing the user does me no favors here.
The Customer Order Error Processing runs a specific stored procedure that I want to be able to identify within my trigger. Knowing this will keep me from modifying additional code from our ERP package.
Your statement "and your connection strings include the app that is connecting" intrigues me. Where would I find the "connection strings"?
I have tried SELECT APP_NAME() but this always returns the same value within our ERP package (customer order maintenance, purchase order maintenance, etc). I believe the only thing that will help me is to know the name of the specific stored procedure causing the update.
March 29, 2011 at 12:18 pm
Here's information on manipulating your connection string.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 30, 2011 at 8:42 am
Just to share my experience (probably it can help you). A while ago I had same issue- could not get apps that updated data in a table. Therefore I added extra column (or you can create extra table for test period and use trigger after update to insert into this test table) with name "AppName" and default value: dbo.getappsname (@@spidid). Function dbo.getappsname is pretty simple- it extracts (from sys.sysprocesses) whatever you need and is not empty or NULL (used COALESCE)- program_name, hostname, nt_username, etc. Good luck!
March 30, 2011 at 1:51 pm
Mike,
Adding columns or altering triggers could lead to support contract violation in some cases. Have you exhausted all other options? You are mentioning dozens of users being able to update the Customer Order table, meaning you are working for a large or medium company. ERP systems on that level almost always have a Workflow option or module. Even if they don’t have Workflow, they almost always have a way of preventing users of creating Orders for suspicious customers.
Some of the answers in this thread are assuming that you own the ERP source code and have access to the internals of the “error processing program”. Is that the case? By the way, what is the “error processing program”? You are referring to it as a job, routine, program that runs stored procedures. Can you put your flag checking logic into those procedures instead altering or adding triggers? Maybe you can use an Application Role for the error processing program if the connection string scenario does not work out for you.
Producing code is always my last resort but here is a link that might help you:
http://vyaskn.tripod.com/tracking_sql_statements_by_triggers.htm
Please note that you will have to use a permanent or global table if you want to run the code the link is pointing to.
April 20, 2011 at 8:57 am
As it turns out, with further investigation into our problem we must modify a different area of this application.
Regardless of that, I sure appreciate all you folks help on this.
Unfortunately, what you have led me to believe for future reference is that there is NO simple answer to this at all. In a nutshell it appears the system does not automatically provide what I am looking for -- the name of the program/stored procedure that is requesting the update to the record that the trigger is processing.
Thanks, all!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply