need help with trigger/cursor

[rudi]

Hi,
When I copy data form MyTable1 into MyTable2, values of OrderNo column has to be equal AutoID( identity column) minus 1.
The below trigger does that.
But it works only with One record.
When I copy more then 1 record it stops working.
Can someone help me how to make it work on multiple insert.
Thanks,
rudi

CREATE TRIGGER mytable_insert2 ON MyTable2 FOR INSERT
AS
declare @ID int
declare @Counter char(6)

select @ID = @@Identity from inserted
select @Counter=@ID

update MyTable2
set OrderNo = @Counter-1
where AutoID = @ID

[Sean Johnston]

You can't use the @@identity to do what you are trying to do. IT only has the value of the last record. In this case, you want to join to 'inserted' which is a virtual copy of the table that the trigger acts on.


------------
rudi at 8/22/00 4:56:03 PM

Hi,
When I copy data form MyTable1 into MyTable2, values of OrderNo column has to be equal AutoID( identity column) minus 1.
The below trigger does that.
But it works only with One record.
When I copy more then 1 record it stops working.
Can someone help me how to make it work on multiple insert.
Thanks,
rudi

CREATE TRIGGER mytable_insert2 ON MyTable2 FOR INSERT
AS
declare @ID int
declare @Counter char(6)

select @ID = @@Identity from inserted
select @Counter=@ID

update MyTable2
set OrderNo = @Counter-1
where AutoID = @ID

[Sean Johnston]

Perhaps this requires a little more explanation. You can not use the @@Identity because the trigger only fires once. If it fired for each insert row then you could use it. But, it does not. The trigger is intended to do bulk operations the entire inserted data set. SQL Server provides a virtual tables called 'inserted' and 'deleted' for use in triggers. In your case you want to use the 'inserted' table.

Try This:

CREATE TRIGGER mytable_insert2 ON MyTable2 FOR INSERT
AS
update MyTable2
set OrderNo = @Counter-1
from MyTable2, inserted
where MyTable2.AutoID = inserted.AutoID

------------
Sean Johnston at 11/2/00 3:21:01 PM

You can't use the @@identity to do what you are trying to do. IT only has the value of the last record. In this case, you want to join to 'inserted' which is a virtual copy of the table that the trigger acts on.


------------
rudi at 8/22/00 4:56:03 PM

Hi,
When I copy data form MyTable1 into MyTable2, values of OrderNo column has to be equal AutoID( identity column) minus 1.
The below trigger does that.
But it works only with One record.
When I copy more then 1 record it stops working.
Can someone help me how to make it work on multiple insert.
Thanks,
rudi

CREATE TRIGGER mytable_insert2 ON MyTable2 FOR INSERT
AS
declare @ID int
declare @Counter char(6)

select @ID = @@Identity from inserted
select @Counter=@ID

update MyTable2
set OrderNo = @Counter-1
where AutoID = @ID