September 25, 2006 at 12:53 pm
I have created a trigger. (My first one) See below. Once an order has been inserted I am trying to get the id from the Inserted table to insert into another table. I trying to figure out why it is taking the id and adding 1 to it. So for example the actual ID is 115683 but it is returning in the insert 115684. I tried using Scope_identity() but that returns nothing. Any help would be appreciated.
Alter
TRIGGER [dbo].[MPI_Insertordxshopevent] ON [dbo].[bvc_Order]
AFter
Insert
AS
--
Begin
Declare
@id int
set
@id=(select id from inserted)
If
(Select Locationid from inserted) <2
RETURN
else
if
(
select locationid from inserted)=2
Begin
Insert
into MPI_orderxshopevent(orderid,shopeventid)
Select
@id,Shopeventid
from
MPI_ShopEvents where eventtype='jobfair' and eventdate in (
Select Max(eventdate)
from MPI_shopevents se
where se.eventtype='Jobfair'
and eventdate<(Select timeoforder from inserted where id=@id))
End
Else
IF
Exists(
Select Locationid from inserted where locationid in (select locationid from MPI_shopeventsxlocation))
Begin
Insert
into MPI_orderxshopevent(orderid,shopeventid)
Select
@id,Shopeventid
from
Inserted I
JOIN
MPI_Shopeventsxlocation sl on I.locationid=sl.LocationID
End
--
else
--
Return
End
September 25, 2006 at 1:08 pm
I think you should
Create trigger [dbo].[MPI_Insertordxshopevent] ON [dbo].[bvc_Order]
FOR insert
Then the trigger will happen when you insert into the table. Then when you read the inserted table, you will get the data that you want to insert.
In BOL
AFTER
Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.
That means if you use 'AFTER INSERT' the trigger will fire after insert into bcv_order is done. If there is an identity field, the field will be the next seq number.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply