Insert Trigger Questions...

[JustBarno]

One of our offices is running a special, the fee for services is less than normal

I don't have access to the code that adds the fee, so I figured a trigger would work.

I'm creating a trigger to change a fee inserted into the database if the purchase originated from that specific office. Ive never created a trigger so bear with me.

I've tried to update the "inserted" table to reflect the fee that I want, but learned that is the wrong approach (see below).

If anybody has an example of similar functionality thank you in advance.

CREATE TRIGGER FixFee ON [dbo].[fees]
FOR INSERT
AS
IF EXISTS
(
SELECT * FROM purchase p JOIN inserted i ON p.file_id = i.file_id WHERE p.site_id = 22
)
BEGIN
Update Inserted SET total = '595.00' WHERE file_id = file_id
END

 

[JustBarno]

Found some help elsewhere

CREATE TRIGGER FixFee ON [dbo].[fees]
FOR INSERT
AS
UPDATE fees
SET total = '595.00'
WHERE EXISTS
(SELECT *
FROM inserted AS i
JOIN purchase AS p
ON p.file_id = i.file_id
WHERE p.site_id = 22
AND i.KeyColumn1 = fees.KeyColumn1
AND i.KeyColumn2 = fees.KeyColumn2)
(untested)

Note that you should replace KeyColumn1 / KeyColumn2 with the names of the
actual columns in the fees table that make up the primary key; reduce or
increase the number of KeyColumn lines to match the number of columns in
the primary key of fees.