AFTER TRIGGER in Oracle

ORACLE AFTER TRIGGER
As the name itself suggests, AFTER the INSERT, UPDATE or DELETE statement is issued, the Oracle database will fire this trigger, or in other words when an INSERT, UPDATE or DELETE statement is issued these triggers can be implicitly executed. On the basis of the Firing Point, it is named as the AFTER Trigger. It can also be used to replace an already existing trigger and thus to change the trigger definition.

Types of the Oracle AFTER trigger:
There are mainly three types of AFTER Trigger in Oracle:

• AFTER INSERT TRIGGER
• AFTER UPDATE TRIGGER
• AFTER DELETE TRIGGER

Limitations of the Oracle AFTER trigger:

• Creation on a view is not possible with the Oracle AFTER trigger.
• OLD values cannot be updated.
• The update feature is only possible for the NEW values.

Syntax:

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT or UPDATE or DELETE
ON table_name
FOR EACH ROW
DECLARE
declarations of variables
BEGIN
trigger code
EXCEPTION
WHEN conditions
exception handling
END;


Parameters:
trigger_name: It is used to specify the name of the trigger to be created.
table_name: It is used to specify the name of the table on which trigger procedure will be executed.

Example:
Students Table:

 STUDENT_ID NAME AGE 1 Joy 20 2 Smiley 19 3 Happy 21 4 James 22 5 Bond 25

Create Trigger code:

CREATE OR REPLACE TRIGGER "STUDENTS_T" AFTER INSERT or UPDATE or DELETE ON "STUDENTS" FOR EACH ROW BEGIN WHEN the person performs insert/update/delete operations into the table. END; / ALTER TRIGGER "STUDENTS_T" ENABLE /

Output:

TRIGGER NAME	TRIGGER TYPE      TRIGGERING EVENT	                    STATUS
STUDENTS_T	       AFTER EACH ROW	   INSERT or UPDATE or DELETE	           ENABLED


Explanation:
The ‘students’ is an already existing table and a trigger is created with the name “STUDENTS_T”. AFTER the INSERT, UPDATE or DELETE statement is issued on the table “STUDENTS”, the Oracle database will fire the trigger.