CodesJava

Easy learning with example program codes

BEFORE TRIGGER in Oracle

ORACLE BEFORE TRIGGER
As the name itself suggests, before 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 BEFORE Trigger. It can also be used to replace an already existing trigger and thus to change the trigger definition.

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

  • BEFORE INSERT TRIGGER
  • BEFORE UPDATE TRIGGER
  • BEFORE DELETE TRIGGER

Limitations of the Oracle BEFORE trigger:

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

Syntax:

CREATE OR REPLACE TRIGGER trigger_name  
BEFORE 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	STUDENT_NAME	STUDENT_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"   
BEFORE 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	BEFORE 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”. Before the INSERT, UPDATE or DELETE statement is issued on the table “STUDENTS”, the Oracle database will fire the trigger.

Please follow and like us:
error









Copyright © 2019 CodesJava DMCA.com Protection Status