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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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