CODEWITHSUNDEEP
sqlsql-servertriggers
Roledenez
Roledenez

Reputation: 761

Insert trigger fires without considering the if condition

CREATE TABLE Account(
    account_no int,
    balance real,
    type_code int,
    branch_no int,
    Bcode int,
    customer_no int,

    CONSTRAINT account_pk PRIMARY KEY (account_no),
    CONSTRAINT check_balance CHECK (balance>=0)
);

alter TRIGGER tr_check_accounts_in_accountTBL
ON account INSTEAD OF INSERT
AS
BEGIN
DECLARE @count int
DECLARE @bcode int
DECLARE @cusNo int

SELECT @bcode=bcode,@cusNo=customer_no
FROM inserted;

SELECT @count=COUNT(a.account_no)
FROM account a
WHERE a.bcode=@bcode AND a.customer_no = @cusNo
print @count;

IF(@count<=5)
    commit transaction
ELSE
        rollback transaction

END

INSERT INTO account(account_no,balance,type_code,bcode,branch_no,customer_no)
VALUES(1,60000,1,1,1,1);

When I try to insert the data to above Account table, trigger fires and rollback the transaction always. I can not understand why. can anyone please to explain thank you.

Error shows as below:

Msg 3609, Level 16, State 1, Line 2
The transaction ended in the trigger. The batch has been aborted.

Upvotes: 1

Views: 366

Answers (1)

marc_s
marc_s

Reputation: 754388

As I said in comments - normally, Inserted can and will contain multiple rows and your current trigger cannot deal with that.

But if you know and can guarantee you're only ever going to insert one row at a time - then your trigger still has flaws:

  • since you're using INSTEAD OF INSERT, in the case where everything is OK, you must actually do the INSERT - this trigger is run instead of the usual SQL insert.... so if all is well, you need to do the insert operation

  • also: don't call COMMIT in the trigger - it will be handled automatically if everything is OK. Only use ROLLBACK to abort an INSERT if the situation is not good

You need to use something like:

CREATE TRIGGER tr_check_accounts_in_accountTBL
ON dbo.Account INSTEAD OF INSERT
AS
BEGIN
    DECLARE @count int
    DECLARE @bcode int
    DECLARE @cusNo int

    SELECT @bcode = bcode, @cusNo = customer_no
    FROM inserted;

    SELECT @count = COUNT(a.account_no)
    FROM dbo.Account a
    WHERE a.bcode = @bcode AND a.customer_no = @cusNo

    IF(@count <= 5)
        -- all is well, now DO the insert!
        INSERT INTO dbo.Account (account_no, balance, type_code, branch_no, Bcode, customer_no)
           SELECT 
              account_no, balance, type_code, branch_no, Bcode, customer_no
           FROM 
              Inserted
    ELSE
       -- situation is not good -> abort the INSERT
       ROLLBACK TRANSACTION
END

Also: I would strongly recommend NOT using real for your money values - use DECIMAL(p, s) instead! REAL is very bad in terms of rounding errors and lack of precision .....

Upvotes: 1

Related Questions