April 26, 2007 at 5:15 am
Hi ppl,
First post so hope its in the right place. Run into a problem.
Have to design a db with its on transaction logging in place.
So have say two tables.
tblContact & tran_tblContact
Have a store procedure that inserts into tblContact. within this storeprocedure there is a userID parameter. This is not stored in the tblContact. I want to store it in the tran_tblContact table.
Is it possible that when the sp is called that the trigger can get the userID? How would i go about making the parameter available to the trigger?
N.
April 26, 2007 at 5:41 am
I'm fairly certain you can only get the columns available from the inserted table within a trigger and not any parameters within a stored procedure. You either have to simply do the auditing from the stored proc or you need to add that column to the table where you're building a trigger.
Now, you can refer to other server objects, CURRENT_USER for example, to get the login information from within a trigger, but this may not help. For example, in most of our systems, we have a single login for our application servers & individual user security is handled at a different layer, so referring to the user connected to the server is pretty meaningless.
"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
April 26, 2007 at 5:49 am
Yes, we have a single login here as well. so that cuts that out. Temp table mite do the job. Cheers for the input.
April 29, 2007 at 2:30 pm
Have the stored procedure insert into the transaction table and then have the trigger insert into the primary table.
I have never done it this way before but it might fit for this scenario.
Mike
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply