0

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 ?

  • 1
    In your oracle trigger, which is fired by an insert into the airline table you execute another insert into the airline table which again fires the trigger. besides tha this causes an infinite loop, what is the sense of trying to insert the same data again in the trigger? you didn't do this in your sqlserver trigger. so tha isn't the same trigger. – miracle173 Aug 2 '17 at 6:17
  • What is your Oracle version ? E.g. 12c introduced identity column. – user272735 Aug 11 '17 at 10:45
1

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).

  • Since, this AFTER INSERT TRIGGER logic works in SQL Server, Won't this logic also work in AFTER INSERT TRIGGER in Oracle PL/SQL ? – stranger Aug 1 '17 at 14:03
  • 1
    They are different systems. My answer is the approach you should take in Oracle – Philᵀᴹ Aug 1 '17 at 14:05
  • 1
    @stranger: you can't modify something in an after trigger in Oracle. This answer is the correct way to do it in Oracle. Don't insist to apply SQL Server best practices on Oracle. – a_horse_with_no_name Sep 26 '18 at 6:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.