Capture Before/After Data from Multirow Update

Problem: I only get one record of before and after data when performing
multirow updates from a single update statement. I want to get before and
after data for ALL updated records from the update statement. How can I do
this? I am using sp_trace_generateevent to capture before and after data i
n
a trace when performing updates using the following code:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR R
EPLICATION AS
BEGIN
PRINT 'HERE'
Declare @.mval nvarchar(256)
Declare @.mval2 nvarchar(256)
Declare @.mvalall nvarchar(512)
SELECT
@.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
' + ' Last Name: ' + au_lname + ' | '
FROM DELETED
SELECT
@.mval2 = ' After First Name: ' + au_fname + ' - ' + '
Last Name: ' + au_lname + ' | '
FROM INSERTED
Set @.mvalall=@.mval + @.mval2
EXEC sp_trace_generateevent
@.event_class = 82, @.userinfo=@.mvalall
ENDYmerejtrebor wrote:
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I d
o
> this? I am using sp_trace_generateevent to capture before and after data
in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + '
-
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
How about inserting direct to a table. This example assumes no nullable
columns are involved and that the key is key_col:
CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION AS
BEGIN
INSERT INTO tracetable
(before_au_fname, before_au_lname, after_au_fname, after_au_lname)
SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
FROM Inserted AS I
JOIN Deleted AS D
ON I.key_col = D.key_col
WHERE I.au_fname<>D.au_fname
OR I.au_lname<>D.au_lname ;
END
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
And also assumes that the primary key value is not changed. I don't think
there is a way to correlate the before/after values for a multi-row update
in that case but one could perform a FULL OUTER JOIN instead of an INNER
JOIN to make sure all changes are recorded (but will NULL before or after
values).
Hope this helps.
Dan Guzman
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1161383771.558910.172880@.i42g2000cwa.googlegroups.com...
> Ymerejtrebor wrote:
>
> How about inserting direct to a table. This example assumes no nullable
> columns are involved and that the key is key_col:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
> REPLICATION AS
> BEGIN
> INSERT INTO tracetable
> (before_au_fname, before_au_lname, after_au_fname, after_au_lname)
> SELECT D.au_fname, D.au_lname, I.au_fname, I.au_lname
> FROM Inserted AS I
> JOIN Deleted AS D
> ON I.key_col = D.key_col
> WHERE I.au_fname<>D.au_fname
> OR I.au_lname<>D.au_lname ;
> END
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Often, the quality of the responses received is related to our ability to
'bounce' ideas off of each other. In the future, to make it easier for us to
give you ideas, and to prevent folks from wasting time on already answered
questions, please:
Don't post to multiple newsgroups. Choose the one that best fits your
question and post there. Only post to another newsgroup if you get no answer
in a day or two (or if you accidentally posted to the wrong newsgroup -and
you indicate that you've already posted elsewhere).
If you really think that a question belongs into more than one newsgroup,
then use your newsreader's capability of multi-posting, i.e., posting one
occurrence of a message into several newsgroups at once. If you multi-post
appropriately, answers 'should' appear in all the newsgroups. Folks
responding in different newsgroups will see responses from each other, even
if the responses were posted in a different newsgroup.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Ymerejtrebor" <Ymerejtrebor@.discussions.microsoft.com> wrote in message
news:B05C6CDD-C085-462C-98C0-1202A26F48CF@.microsoft.com...
> Problem: I only get one record of before and after data when performing
> multirow updates from a single update statement. I want to get before and
> after data for ALL updated records from the update statement. How can I
> do
> this? I am using sp_trace_generateevent to capture before and after data
> in
> a trace when performing updates using the following code:
> CREATE TRIGGER [AUDITED] ON [dbo].[authors] FOR UPDATE NOT FOR
REPLICATION
> AS
> BEGIN
> PRINT 'HERE'
> Declare @.mval nvarchar(256)
> Declare @.mval2 nvarchar(256)
> Declare @.mvalall nvarchar(512)
> SELECT
> @.mval = ' UPDATE: Before First Name: ' + au_fname + ' -
> ' + ' Last Name: ' + au_lname + ' | '
> FROM DELETED
> SELECT
> @.mval2 = ' After First Name: ' + au_fname + ' - ' + '
> Last Name: ' + au_lname + ' | '
> FROM INSERTED
> Set @.mvalall=@.mval + @.mval2
> EXEC sp_trace_generateevent
> @.event_class = 82, @.userinfo=@.mvalall
> END
>|||Dan Guzman wrote:
> And also assumes that the primary key value is not changed. I don't think
> there is a way to correlate the before/after values for a multi-row update
> in that case but one could perform a FULL OUTER JOIN instead of an INNER
> JOIN to make sure all changes are recorded (but will NULL before or after
> values).
>
Unless there is a key value (not necessarily the primary key) that
remains unchanged then it isn't possible to correlate row values before
and after the update. That is logical enough. Rows are identifiable
only by their keys so it is all but meaningless to talk of a row
changing its key value.
Some DBMSs do allow the before and after values to be correlated
without a key. Oracle for example has a FOR EACH ROW trigger that does
exactly that. An unfortunate side effect may be that the end result
depends on some internal physical state that isn't fully exposed
anywhere in the logical model. The real solution is to ensure that
every update operation preserves enough information to make the logical
meaning explicit. If you do that then key change should not be any
problem at all.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--