Re: Capture Before/After data on Multirow Updates

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


"Ymerejtrebor" <Ymerejtrebor@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:CD971923-195E-464C-A51B-E447526EF7ED@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: general network error
    ... I looked at errors log too - there are no errors. ... The code would not tell you anything it is just an update statement but ... declare @error integer ... DECLARE @return_code INTEGER ...
    (comp.databases.ms-sqlserver)
  • Re: Oracle Stored Proc issues
    ... I use the value in the update statement. ... G Quesnel wrote: ... declare the text variable to be some extra large number, ... string of text to the variable, then the variable will only contain ...
    (comp.databases.oracle.misc)
  • Number of rows updated
    ... the block is an update statement. ... DECLARE var_a number; ... a bunch of sql statement go here; ...
    (comp.databases.oracle.server)
  • Re: Output and return from stored procedure
    ... > Declare another variable and Set the @@ROWCOUNT to this varibale ... > after your update statement. ...
    (microsoft.public.sqlserver.programming)