The multi-part identifier "i.charged_amount" could not be bound

I am trying to write a trigger where if a client tries to purchase something, but if they pass their credit card limit, the transaction is cancelled.

my tables are:

customers(name,ssn,code),

accounts(customer_code,acc_number,balance,rate),

creditcard(issued,expired,limit,balance,cc_number),

transactions(date,cc_number,charged_amount,conf_number,shop_code)

And what I wrote is

CREATE TRIGGER check_balance ON transactions
FOR INSERT
AS
    DECLARE @balance int, @limit int

    SELECT @balance = balance, @limit = limit
    FROM creditcard
    INNER JOIN inserted i ON creditcard.cc_number = i.cc_number

    IF (@balance + i.charged_amount > @limit)
    BEGIN        
        ROLLBACK TRANSACTION
    END

But I get a

Msg 4104, Level 16, State 1, Procedure check_balance, Line 10 The multi-part identifier "i.charged_amount" could not be bound. where line 10 is IF (@balance + i.charged_amount > @limit)

I know that 开发者_开发百科the message means that I can't use i.* , cause the scope of it is only in the select..from.

I tried using referencing new row as I, but I got a syntax error near referencing. I am using SQL Server 2005.

Could anyone please help me?

Your assignment to scalar variables won't work with multi row inserts. inserted is a table that may contain any number of rows.

An insert may even contain multiple different rows for the same credit card that individually are OK but collectively would exceed the account limit.

The logic would need to be something like...

IF EXISTS
(
SELECT c.cc_number
FROM creditcard c
INNER JOIN inserted i ON c.cc_number = i.cc_number
GROUP BY c.cc_number, c.limit, c.balance
HAVING c.balance + SUM(i.charged_amount) > c.limit
)

ROLLBACK...

try this:

create trigger check_balance on transactions

for insert
as

IF EXISTS (SELECT 1 
           FROM creditcard 
               INNER JOIN inserted i ON creditcard.cc_number = i.cc_number 
           GROUP BY c.cc_number
           HAVING MIN(creditcard.balance)+SUM(i.charged_amount)>MIN(creditcard.limit)
          )
BEGIN
    raiserror ('bad limit found',16,1)
    ROLLBACK TRANSACTION
    return
END

I think the problem is that you are trying to use the alias i when it is only in scope for the query you aliased it in. Kindly change the reference in the IF statement to inserted instead of i.