Oracle After Insert Trigger
In Oracle the AFTER INSERT
Trigger will fire after the INSERT operation is executed.
Oracle After Insert Trigger Syntax
Syntax
CREATE [OR REPLACE ] TRIGGER trigger_name
AFTER INSERT ON table_name
[FOR EACH ROW]
DECLARE
Declaration-statements
BEGIN
Executable-statements
EXCEPTION
Exception-handling-statements
END;
Where:
CREATE TRIGGER trigger_name: To create trigger with sutable name.
REPLACE OR REPLACE TRIGGER trigger_name: To replace trigger with sutable name.
AFTER: To specifies when you want the trigger to be executed. The INSTEAD OF clause is used for creating trigger on a view.
INSERT: To specifies DML operation.
[ON table_name]: To specifies the name of the table associated with the trigger.
[FOR EACH ROW]: To specifies a row level trigger, Otherwise the trigger will execute just once when the SQL statement is executed.
WHEN (condition): The condition on which the trigger would fire. This is valid for row level triggers.
Oracle After Insert Trigger Example
To show how to create an AFTER INSERT trigger using the CREATE TRIGGER statement, use the following syntax:
CREATE OR REPLACE TRIGGER user_before_insert
AFTER INSERT
ON tblCustomerLoan
FOR EACH ROW
DECLARE
t_username varchar2(10);
BEGIN
-- Find username of person performing the DELETE on the table
SELECT user INTO t_username
FROM dual;
-- Insert record into tblUser table
INSERT INTO tblUser
( firstName,
lastName,
address,
delete_date,
deleted_by )
VALUES
( :old.firstName,
:old.lastName,
:old.address,
sysdate,
t_username );
END;