CODEWITHSUNDEEP
sqlsql-server
user3740970
user3740970

Reputation: 389

How do I write a trigger that limits that a person from the bankcustomer table can only have 3 accounts in the account table?

I have a bankcustomer table which looks like this:

create table bankcustomer
(
    cpr char(10) primary key,
    name varchar(30) not null
)

And an account table which looks like this:

create table account
(
    accountnr int identity(1001,1) primary key,
    accountowner char(10) foreign key references bankcustomer, 
    created date not null,
    balance decimal(14,2) not null
)

I want to write a trigger in SQL Server that limits a bankcustomer so that a bankcustomer can have no more than 3 accounts in the account table.

I create an account for a specific bank customer by inserting a value in the accountowner column in the account table that matches a cpr from the bankcustomer table (when inserting a record into the account table).

I have this code so far:

create trigger mytrigger4
on account
for insert
as
    if exists (select count(*) 
               from inserted 
               join bankcustomer on inserted.accountowner = bankcustomer.cpr 
               where cpr = inserted.accountowner 
               having count(*) > 3)
    begin
        rollback tran
        raiserror('A customer must have a maximum of 3 accounts', 16, 1)
    end
go

The problem is that I can keep creating accounts (insert records in the account table) for a customer even though the customer already has 3 accounts. Which means the code in the trigger does not work at all.

Any help would be appreciated!

Upvotes: 0

Views: 157

Answers (1)

SMor
SMor

Reputation: 2862

Let's think about your code. First, it is apparent that you test using single row inserts only. And that probably carries over into your sql code generally. That's bad, because an insert (or update or delete or merge) can affect any number of rows. While you can expect that the majority of inserts from an application are likely to be single rows, there are always situations that affect multiple rows. And that is an assumption that should always be in your mind when writing sql code generally - and triggers specifically.

Your test is based on exists. That is testing for the existence of rows generate by the query inside the exists clause. So - look carefully at your query. First, you count but do not group. Therefore, you are counting all the rows generated by the query. This is incorrect because of the assumption mentioned earlier. But let's ignore that for the moment and examine the select statement alone.

Your select statement joins inserted to the parent bankcustomer. The join is correct but why is there a where clause? And let's sidetrack into best practices. Always - ALWAYS - give each table a useful alias and use that alias when referencing columns. Why? Because this makes it easier for others to read and understand your query. BTW - a useful alias is not a single character. Yep - writing code can be a little work.

Let's continue. Your query counts all rows in the resultset. If you insert a single row, what is the result of your join? We know that an account is associated with a single bankcustomer. So when you insert a single row into account, the join will produce A SINGLE ROW. And counting that single row resultset will always produce a single row with the value of - tadah - 1. Now there is a way to cause your trigger generate an error. Insert 4 or more rows with a single statement. The error will probably not be accurate, but it will kill the transaction and display a message.

So you see that your logic is flawed. You need to count rows in the actual table (account), not inserted. But not all the rows - because that would be inefficient. You just need to consider all rows that "share" the accountowner values found in inserted. Note the plural "values". This is where your single row assumption fails. So, how to do that? Here is one way to write your trigger. Note that the first query is included to let you "see" what the count query is producing - this is for debugging only. Production triggers should never return a resultset in any fashion. 

alter trigger mytrigger4
on account
for insert
as begin

    select cust.cpr, count(*) 
               from bankcustomer as cust join account as acc on cust.cpr = acc.accountowner
               where exists (select * from inserted as ins where ins.accountowner = cust.cpr)
               group by cust.cpr;

    if exists (select cust.cpr, count(*) 
               from bankcustomer as cust join account as acc on cust.cpr = acc.accountowner
               where exists (select * from inserted as ins where ins.accountowner = cust.cpr)
               group by cust.cpr
               having count(*) > 3)
    begin
        rollback tran
        raiserror('A customer must have a maximum of 3 accounts', 16, 1)
    end
end;
go

I'll leave it to you to actually test is thoroughly - which includes the use of insert statements that insert multiple rows. And, of course, you will vary the test data to include customers that have no accounts, less than 3 accounts, exactly 3 accounts, and more than 3 accounts (because sometimes things happen and extra accounts get added despite your best efforts).

Upvotes: 1

Related Questions