sql server mseq: Capture Before/After data on Multirow Updates -- sql server mseq

Capture Before/After data on Multirow Updates

Ymerejtrebor
10/20/2006 2:57:01 PM

sql server mseq:

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

Re: Capture Before/After data on Multirow Updates

Arnie Rowland
10/20/2006 4:43:06 PM

You can't assign multiple row values to a single variable. SQL just returns
the first row values -as you noticed.

For your test, change the code to

SELECT * FROM inserted
SELECT * FROM deleted

to see what is in the tables.

Provide more detail about what you are hoping to accomplish and we can help
you better.

--
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

[quoted text, click to view]