CODEWITHSUNDEEP
sqlsql-servertriggersinsert
k.atli
k.atli

Reputation: 33

SQL Server : trigger after insert, where insert affects two tables

I have 3 tables:

I want to make a trigger that checks that 

TRANSACTION.Amount+CARD.Balance is not greater than CARD.Credit_Line

every time I insert a new transaction.

I have already try to do the following and the trigger is acceptable from SQL Server, but it seems that it does not work when I insert a new transaction.

Here is my code:

CREATE TRIGGER check_b
ON TRANSACTION
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @balance MONEY,
            @credit_line MONEY,
            @amount MONEY

    SELECT @balance = Balance, @credit_line = Credit_Line, @amount = amount
    FROM TRANSACTION, USED, CARD
    WHERE TRANSACTION.number = USED.transn 
      AND USED.cardn = CARD.number 

    IF (@balance + @amount > @credit_line)
    BEGIN
        ROLLBACK TRANSACTION
    END
END

I expect an error from SQL Server when I insert a new Transaction, but for some reason the trigger does not do the job.

UPDATE!

Here is my new code:

CREATE TRIGGER check_b
ON TRANSACTION
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @amount MONEY

    SELECT @amount = amount
    FROM TRANSACTION, USED, CARD
    WHERE TRANSACTION.number = USED.transn 
      AND USED.cardn = CARD.number 

    IF (SELECT Balance FROM Card) + @amount > (SELECT Credit_Line FROM CARD)
    BEGIN
        ROLLBACK TRANSACTION
    END
END

The error that I get is:

Msg 512, Level 16, State 1, Procedure check_b, Line 11
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Upvotes: 0

Views: 76

Answers (1)

GDS
GDS

Reputation: 56

So i did the same setup at my end and was able to reproduce your problem. The issue is on every trigger fire, it is only comparing the 'currently inserted' transaction.amount to the card number credit line. if you look at my setup, I was able to insert transaction 51 successfully despite the sum of amount of transaction 50 & 51 + balance = 1350 which is > 1000 (I think you are expecting 51 to get rolled back in your case), when i inserted transaction 60, the triggers rightly rolled it back because the amount (1000) + balance (500) of this single transaction was greater then 1000 of credit line

PS: Code needs better naming convention of objects, transactions and errors need to be handled better. see my_setup

Upvotes: 1

Related Questions