x

Profiler Query

I have a trigger that writes data to a history table, one field in this table is the iUserID field. This field is taken from the iUserID field in the Inserted table, however in actual fact it's taking the data from an old history record rather than the person who actually updated the data. I have run a trace to see where this data is changing and all I'm getting back is the trigger that's firing, this is correct but I think there must be an action prior to this that I'm missing on the trace that's updating the table and therefore populating the Inserting table when I run the update. Can someone please advise me on what I should tick to ensure that all scripts run are returned. I have already ticked under stored procedures the SP:StmtComplted and SP:StmtStarting columns to ensure I get the triggers that are fired.

Please let me know if you need more information, I know this looks messy but I can't but the code in as it's 2500 lines long.
more ▼

asked Mar 07 '11 at 07:20 AM in Default

Mrs_Fatherjack gravatar image

Mrs_Fatherjack
4.7k 57 62 66

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
is the trigger looking for more than one row in the Inserted table? - http://msdn.microsoft.com/en-us/library/ms190752.aspx
more ▼

answered Mar 07 '11 at 08:13 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.3k 73 77 107

No, there should only be 1 row affected.
Mar 07 '11 at 08:17 AM Mrs_Fatherjack

wholly possible that is expected. Does the trigger handle if there are more than one though? It ought to regardless of the current issue...

Where is the 'old history record' coming from then? A separate select somewhere or a join?
Mar 07 '11 at 08:20 AM Fatherjack ♦♦
A separate select.
Mar 07 '11 at 08:24 AM Mrs_Fatherjack
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x34
x18

asked: Mar 07 '11 at 07:20 AM

Seen: 666 times

Last Updated: Mar 07 '11 at 07:20 AM