Problem with Insert trigger

I have an insert trigger that works 99.999% of the time. The trigger is
fired via a dts process that runs every 10 minutes which inserts data into a
ForecastTonnageChanges table. Based on changes to this
ForecastTonnageChanges, I want to update the live Forecast table. The
trigger fires this event. My basic logic in the trigger is:
Delete from Forecast
where exists (select *
from inserted
where <joining key columns> )
Insert into Forecast
select ...
from inserted
When this doesn't work, I am ending up with extra records in my forecast
table. I'm wondering if another insert has happened and between the delete
and the insert in the trigger, an additional row is in the inserted table.
Is this possible? I would think implied locking via the trigger would
prevent this. But, I'm grasping for straws here.
If you have ideas on a more reliable way to implement this... please share.Hi
A trigger is fired for each statement, therefore you will not get extra rows
in the inserted/deleted tables.
As you don't give DDL and all the trigger code it is hard to say where you
are going wrong, but you may want to use profiler to see what is happening.
You should also implement error handling to make sure that the statement
succeeds and all the relivent statements in the transaction are rolled back
if a failure occurs.
John
"Erin" wrote:
> I have an insert trigger that works 99.999% of the time. The trigger is
> fired via a dts process that runs every 10 minutes which inserts data into
a
> ForecastTonnageChanges table. Based on changes to this
> ForecastTonnageChanges, I want to update the live Forecast table. The
> trigger fires this event. My basic logic in the trigger is:
> Delete from Forecast
> where exists (select *
> from inserted
> where <joining key columns> )
> Insert into Forecast
> select ...
> from inserted
> When this doesn't work, I am ending up with extra records in my forecast
> table. I'm wondering if another insert has happened and between the delet
e
> and the insert in the trigger, an additional row is in the inserted table.
> Is this possible? I would think implied locking via the trigger would
> prevent this. But, I'm grasping for straws here.
> If you have ideas on a more reliable way to implement this... please share.[/color
]|||Without any more information, could you have a forecast VIEW of the
most current rows in the ForecastTonnageChanges table instead of
physically shuffling all this data around? You obviously have a time
stamp on the new data, so that should be easy enough.