Update trigger: @@rowcount and count(*) from inserted mismatch 
Author Message
 Update trigger: @@rowcount and count(*) from inserted mismatch

Please consider following script:

create table tbljunk (col1 int null)
go

create table tbljunk1 (col1 int, col2 int null)
go

insert tbljunk
select 1
go

insert tbljunk1 values (1, 2)
go

insert tbljunk1 values (1, 3)
go

create trigger x1 on tbljunk for update
as
begin

  select count(*) from inserted
end
go

update tbljunk
set col1 = col2
from tbljunk j, tbljunk1 j2
where j.col1 = j2.col1
go


should be same (in this case 1) but if you run the script you will get them
1 and 2 respectively.

I have tried this on SQL 6.5 SP3 and SP4. Is this a bug or my understanding
problem?

Thanx



Mon, 01 Jan 2001 03:00:00 GMT
 Update trigger: @@rowcount and count(*) from inserted mismatch

I think what you're seeing is a result of the update statement's where
clause finding two qualifying rows but only one row to update.  If you
change the values of the second insert to tbljunk1 to (2, 2), you'll get a

Multi-row updates can be difficult to deal with in triggers.  If you have a

will be changed (or an attempt will be made to change them) to the first
corresponding value in the inserted table.

trigger as operations within the trigger will change its value.

--
Bob Pfeiff
MCSD, MVP
Spectrum Technology Group, Inc.



Mon, 01 Jan 2001 03:00:00 GMT
 Update trigger: @@rowcount and count(*) from inserted mismatch

Quote:

>...........

>should be same (in this case 1) but if you run the script you will get them
>1 and 2 respectively.

>I have tried this on SQL 6.5 SP3 and SP4. Is this a bug or my understanding
>problem?

Reprorduced it in 6.5 SP3.  Contents of both inserted and updated is


update operation ) is correctly reported as 1.  I think it's a bug.  

Have not tried it in 7.0 'cause I will only install the beta3 sometime
this afternoon.

Isaac



Tue, 02 Jan 2001 03:00:00 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Create Trigger - Insert, Update - not updating record on Insert

2. @@rowcount = count(*) from inserted?

3. puzzling issue with inserted, updated and IF UPDATE(column) in an update trigger

4. @@ROWCOUNT value when using INTEAD OF trigger for update (2000)

5. Update Trigger and ChildID Insert Trigger

6. INSERT trigger versus UPDATE/DELETE trigger

7. Which record triggers an INSERT, UPDATE Trigger

8. Updating Inserted record in Insert Trigger?

9. Insert/Update Trigger - multiple inserts.

10. INSERT trigger updating the inserted row

11. newbie : on insert triggers can i update the row i just inserted

12. Insert/Update Trigger performing Updates


 
Powered by phpBB® Forum Software