Oracle Tutorial

What is Oracle
SQL Keywords

Oracle Wildcards

Oracle Wildcards

Oracle Aliases

Oracle Aliases

MySQL Tutorial

MySQL Tutorial

PL/SQL Tutorial

PL/SQL Tutorial

Oracle Interview Questions

Oracle Interview Questions and Answers


Oracle After Delete Trigger

In Oracle the AFTER DELETE Trigger will fire after the DELETE operation is executed.

Oracle After Delete Trigger Syntax

Syntax

CREATE [OR REPLACE ] TRIGGER trigger_name  
	AFTER DELETE 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.

DELETE: 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 Delete Trigger Example

To show how to create an AFTER DELETE trigger using the CREATE TRIGGER statement, use the following syntax:

CREATE OR REPLACE TRIGGER user_before_insert
AFTER DELETE
   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;