SQL Trigger not working correctly

Question

here are the 2 tables i have, i want to implement an trigger that customer cannot have more than 5 accounts from a one bank, but can have more than 5 in total.

  CREATE TABLE ACCOUNT(
   ACCOUNT_NO VARCHAR(20) NOT NULL,
   BALANCE REAL,
   BANK_CODE VARCHAR(20),
   BRANCH_NO VARCHAR(25),
   ACCOUNT_CODE VARCHAR(20),
   PRIMARY KEY(ACCOUNT_NO),
 );

 CREATE TABLE ACCOUNT_CUSTOMER(
   CUS_NO VARCHAR(20) NOT NULL,
   ACCOUNT_NO VARCHAR(20) NOT NULL,

   PRIMARY KEY(CUS_NO,ACCOUNT_NO),
       FOREIGN KEY(ACCOUNT_NO) REFERENCES ACCOUNT(ACCOUNT_NO),
   );

heres the trigger i wrote but i can't create more than 5 accounts in total because it checks for all the accounts in all the banks rather than a single bank.

    CREATE TRIGGER TRIGGER1
    ON ACCOUNT_CUSTOMER 
    FOR INSERT,UPDATE
    AS BEGIN
    DECLARE @COUNT INT
    DECLARE @CUS_NO VARCHAR(20)

    SELECT @COUNT=COUNT(AC.ACCOUNT_NO)
    FROM INSERTED I,ACCOUNT_CUSTOMER AC
    WHERE I.CUS_NO=AC.CUS_NO
    GROUP BY(AC.CUS_NO)

    IF @COUNT>5
    ROLLBACK TRANSACTION
    END

THE PROBLEM IS WITHIN THE GROUPBY FUNCTION AS I GUESS.

Answer 1

I would try something like this:

Replace this part of your trigger

SELECT @COUNT=COUNT(AC.ACCOUNT_NO)
FROM INSERTED I,ACCOUNT_CUSTOMER AC
WHERE I.CUS_NO=AC.CUS_NO
GROUP BY(AC.CUS_NO)

IF @COUNT>5
ROLLBACK TRANSACTION

with this:

IF EXISTS (
        SELECT COUNT(a.ACCOUNT_NO)
        FROM INSERTED i
                JOIN ACCOUNT a ON i.ACCOUNT_NO = a.ACCOUNT_NO
                JOIN ACCOUNT_CUSTOMER c ON i.CUS_NO = c.CUS_NO
        GROUP BY c.CUS_NO, a.BANK_CODE
        HAVING COUNT(a.ACCOUNT_NO) >= 5
    )
    ROLLBACK TRANSACTION

Also consider that the INSERTED table may have multiple records in it. If those records are for more than one customer and any of the customers causes this trigger to rollback the transaction, then the updates for those customers that did not violate your rule will not be applied. This may never happen (if your application never updates records for more than one customer at a time), or may be the intended behavior.