How to execute AFTER INSERT TRIGGER with an auto-incremented (seq.NEXTVAL) column value in Oracle PL/SQL

oracleplsqltrigger

Below is my AFTER INSERT TRIGGER in Microsoft SQL Server:

   CREATE TRIGGER new_airline
   ON Airline
   AFTER INSERT
   AS
   BEGIN
   DECLARE @Airline_Name VARCHAR(50), @Country VARCHAR(50)
   SELECT @Airline_Name=Airline_Name, @Country=Country
   FROM INSERTED
   PRINT 'New Airline has been added'
   END

   INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

The above AFTER INSERT TRIGGER in Microsoft SQL Server works successfully. Another thing to be noted that there is also Airline_No column in the Airline table for which I didn't have to insert any values because Airline_No column has been set to AUTO-INCREMENT with INTEGER IDENTITY (1,1). Hence, the value will be auto generated everytime a new row is inserted.

Now, I want to execute this same AFTER INSERT TRIGGER logic in Oracle PL/SQL which is shown below:

CREATE OR REPLACE TRIGGER new_airline 
AFTER INSERT ON Airline
FOR EACH ROW
BEGIN
INSERT INTO Airline (Airline_No, Airline_Name, Country)
VALUES (:NEW.Airline_No, :NEW.Airline_Name, :NEW.Country);
DBMS_OUTPUT.PUT_LINE('New Airline has been added');
END;

The above TRIGGER got compiled successfully but it was showing error when I was trying to insert the values which is shown below:

INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL, 'Malaysia Airlines', 'Malaysia');

When I was trying to insert the values in Airline table I was getting the following error message which is shown below:

Error starting at line : 10 in command -
INSERT INTO Airline (Airline_No,Airline_Name, Country)
VALUES (airline_seq.NEXTVAL,'Malaysia Airlines', 'Malaysia')
Error report -
ORA-04091: table DEMO.AIRLINE is mutating, trigger/function may not see it
ORA-06512: at "DEMO.NEW_AIRLINE", line 2
ORA-04088: error during execution of trigger 'DEMO.NEW_AIRLINE'

Is there any logic in ORACLE PL/SQL AFTER INSERT TRIGGER that I possibly might have missed out ?

Best Answer

The trigger you have written will try and insert a second row, and is causing the triggers view of the table to mutate (as it will end up in an infinite loop, essentially).

You actually only need a BEFORE INSERT trigger that sets :NEW.Airline_No : 

create trigger new_airline_btrig 
  before insert on airline
  for each row
begin
  :new.airline_id := airline_seq.NEXTVAL;
end;
/

You can then insert using just:

INSERT INTO Airline (Airline_Name, Country)
   VALUES ('Malaysia Airlines', 'Malaysia')

:new.columnname alters the given inserted value (in this case, from NULL to the sequence value, as airline_id was not provided).

Related Question