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]
FOR INSERT AS
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!