| Arnie Rowland 2006-10-24, 6:49 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
"Ymerejtrebor" < Ymerejtrebor@discuss
ions.microsoft.com> wrote in message
news:CD971923-195E-464C-A51B- E447526EF7ED@microso
ft.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_generateeve
nt 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_generateeve
nt
> @event_class = 82, @userinfo=@mvalall
> END
>
|