TRIGGER problem? Does DTS affect Triggers?

Last Post 16 Jul 2010 01:34 AM by dstoltz. 2 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
New Member
New Member

15 Jul 2010 02:43 AM
I need some help - I have a table called BEDS, and RESERVATIONS.

The BEDS table gets new data 2 possible ways:

1) Every 60 seconds the table gets truncated, and repo***ted from an AS400 data source via a DTS package
2) Might get a row updated from a web application

If I want to update the reservations table (actually delete data based on the beds table) I would think #1 would need an INSERT trigger, and #2 would need an UPDATE trigger....

These triggers are in place, and the update trigger seems to be working, but it seems when the DTS package runs, either the INSERT trigger isn't firing, or the INSERT code is faulty.

So my two questions are:

1) Will the INSERT trigger fire for a DTS package using transform data from the AS400 to SQL?

2) Is my trigger logic ok?

I've put the INSERT trigger below. It should be grabbing the data coming in, and if the RUSR5 field does NOT equal an M or F, it should do a delete on the reservations table:

CREATE TRIGGER INSERT ClearReservations ON [dbo].[beds]
DECLARE @rusr5 varchar(5), @rnrst varchar(10), @room varchar(10), @bed varchar(10)

SELECT @rnrst=rnrst, @room=room, @bed=bed, @rusr5 = rusr5 FROM INSERTED

IF(@rusr5<>'M' AND @rusr5<>'F')

   DELETE FROM reservations WHERE (rnrst=@rnrst AND room=@room AND bed=@bed)


Thanks for any help!
New Member
New Member

15 Jul 2010 10:08 AM
Triggers don't fire on a data pump task. If you need 'em to fire, pump to a staging table, then use an execute sql task to insert the data to the live table

INSERT LiveTable SELECT * FROM StagingTable

The insert will fire your trigger
New Member
New Member

16 Jul 2010 01:34 AM
Thanks for that confirmation....I was afraid of that....good solution as well...


Acceptable Use Policy