Oracle SQL Creating Trigger to Increment a Sequence Number For Each Row

Question

I'm trying to create a trigger but I have learned I can not design it as in my first attempt, which I'm showing below. This will cause a 'mutating table' error due to selecting from the table as it is being modified. It actually didn't cause this error when inserting only one record at a time, but when I insert multiple records at once it does.

The purpose of the trigger is to count the number of records in the table where the customer is equal to the customer about to be inserted, and to set the new order_num value as count+1. I also have a public key value set by the trigger which draws from a sequence. This part works ok once I remove the order_num part of the trigger and the associated SELECT. How can I achieve what I am trying to do here? Thanks in advance.

CREATE OR REPLACE TRIGGER t_trg
    BEFORE INSERT ON t
    FOR EACH ROW
DECLARE
    rec_count NUMBER(2,0);
   BEGIN
     SELECT COUNT(*) INTO rec_count
     FROM t
     WHERE customer_id = :NEW.customer_id;

     :NEW.order_num:= rec_count+1;
     :NEW.order_pk_id:= table_seq.NEXTVAL;

   END;

Answer 1

Two triggers and temp table approach can provide solution to you seek, preventing mutating table error. However performance will most likely suffer.

create global temporary table cust_temp(customer_id number, cust_cnt number);

create or replace trigger t_trig1
before insert on t
declare
begin
  insert into cust_temp select customer_id, count(*) from t group by customer_id;
end;
/

CREATE OR REPLACE TRIGGER t_trg2 
    BEFORE INSERT ON t
    FOR EACH ROW
DECLARE
    rec_count number;
   BEGIN
     BEGIN
       SELECT cust_cnt INTO rec_count
       FROM cust_temp
       WHERE customer_id = :NEW.customer_id;
    EXCEPTION when no_data_found then rec_count := 0;
    END;
     :NEW.order_num:= rec_count+1;
     :NEW.order_pk_id:= table_seq.NEXTVAL;
      update cust_temp set cust_cnt = rec_count + 1 
      where customer_id = :NEW.customer_id;

   END;
  /