CODEWITHSUNDEEP
t-sqltriggers
Guttsy
Guttsy

Reputation: 2119

T-SQL Trigger After Delete

Situation: Let's say you have a database of organizations, students, and course purchases, and an administrator wants to get a refund for a course they assigned out. The software doesn't support refunds by default. You don't have access to the source code, but you can set up triggers in the database.

Database Structure:

General idea is that, when an entry (or more than one!) is removed from the purchases table, a trigger runs to update the account balance of the appropriate organization. amount_to_refund can be null or zero, so no refund is given in that case. Refunds should also not be given if time_of_purchase was more than 30 days ago.

Any ideas how to pull this off? I've been modeling it off of another trigger, but am getting thrown off by the UPDATE ... FROM ... syntax, which I can't say I've used before. I have looked at MSDN, but I'm a bit overwhelmed.

Optionally, I'd also like to insert rows into another table (not documented here) containing the refund amount and organization ID. I just need a general idea where this fits in and can probably handle the rest myself.

Upvotes: 0

Views: 5834

Answers (1)

rsenna
rsenna

Reputation: 11963

CREATE TRIGGER [dbo].[TrgPurchasesDelete] ON [dbo].[Purchases] FOR DELETE
AS
BEGIN
    UPDATE
        [dbo].[Organizations]
    SET
        account_balance = account_balance + DLTD.ammount_to_refund
    FROM
        [dbo].[Organizations] ORGA
        INNER JOIN [dbo].[Students] STDT ON
            STDT.organization_id = ORGA.organization_id
        INNER JOIN DELETED DLTD ON
            DLTD.student_id = STDT.student_id
    WHERE
        DLTD.ammount_to_refund > 0
        AND DLTD.time_of_purchase > DATEADD(DAY, -30, SYSDATE)
END
GO

Upvotes: 1

Related Questions