Oracle and mutating table with simple exercise

Question

I'm in trouble with the implementation of a trigger. Assuming that I have two types:

CREATE TYPE customer_t AS OBJECT( 
code INTEGER,
name VARCHAR(20),
surname VARCHAR(20),
age INTEGER);

and the type

CREATE TYPE ticket_t AS OBJECT (
price INTEGER,
cust REF customer_t
)

And then I have the associate tables:

CREATE TABLE customers OF TYPE customer_t

CREATE TABLE tickets OF TYPE ticket_t

I have to do an exercise so I have to create a trigger for ensure that a customer won't buy more than 10 tickets but, if I use command like "select count(*)" I get an error because I can't access to mutating table.

Please can anyone help me with this trigger?

EDIT:

I populated the tables as follows:

INSERT INTO custs (code, name, surname, age) values (123, 'Paolo', 'Past', 32);

and repeating the following operation ten times:

INSERT INTO tickets (price, cust) values 
(4, (SELECT * FROM (SELECT REF(T) FROM custs T WHERE name = 'Paolo' AND surname = 'Past') WHERE rownum < 2))

The trigger implemented is:

create or replace
trigger check_num_ticket after insert on tickets
for each row
declare 
   num_ticket number;
begin
SELECT count(*) INTO num_ticket FROM tickets WHERE :new.cust = cust;
if (num_ticket >= 10) then
  raise_application_error('-20099', 'no ticket available');
end if;
end;

And I get this error:

A trigger (or a user defined plsql function that is referenced in
this statement) attempted to look at (or modify) a table that was
in the middle of being modified by the statement which fired it.

Answer 1

You are getting the mutating table error, because you are inserting in the same table where you want to get the row count for. Imagine your insert statement inserts two rows. There is no rule which row to insert first and which last, but your trigger fires on one inserted row and wants to know how many rows are already in the table. The DBMS tells you this is undefined, as the table is currently mutating.

You need an after statement trigger instead of a before row trigger. So when the insert statement's inserts are done, you look at the table to see whether there are suddenly customers with too many rows in it.

(A great alternative is a compound trigger. It combines row and statement triggers. So in the after row section you'd remember the customers in some array/collection and in the after statement section you'd look up the table for only the remembered customers.)