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 ?