PL/SQL Exception

PL/SQL Exception

PL/SQL Interview Questions

PL/SQL Interview Questions and Answers


PL/SQL After Insert Trigger

In PL/SQL the AFTER INSERT Trigger will fire after the INSERT operation is executed.

PL/SQL 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.

PL/SQL 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;