Exploring the Types of Oracle Triggers（oracle触发器类型）

Oracle databases offer various types of triggers, which are essential Database Transaction Language (DTL) elements. These triggers are associated with tables and can be applied to data manipulation language (DML) commands such as Insert, Update and Delete.Triggers are created using a set of SQL statements and it is useful for automating tasks.

There are three types of database triggers -Before trigger, After trigger and Instead of trigger. Before trigger are designed to fire before data modification statements like an Insert and Update. After triggers fire after data is modified and Instead of triggers replace the triggering action of the DML statement.

The syntax of creating a Oracle Before trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name
 BEFORE INSERT OR UPDATE OR DELETE
ON tablename
 [FOR EACH ROW]
BEGIN 
  --trigger code 
END;
/ 

Here, we are creating a trigger that will fire before an insert or an update to a table. The trigger should perform some action when an insert or update occurs.

The syntax of creating a Oracle After trigger is :

CREATE [ OR REPLACE ] TRIGGER trigger_name
 AFTER INSERT OR UPDATE OR DELETE
ON tablename 
 [FOR EACH ROW] 
BEGIN
  --trigger code 
END;
/ 

This trigger fires as soon as the action of the insert/update statement has been successfully executed. The trigger could be used to perform some action based on the modified data.

The syntax of creating a Oracle Instead of trigger is:

CREATE [OR REPLACE ] TRIGGER trigger_name 
INSTEAD OF INSERT OR UPDATE OR DELETE 
ON tablename 
 [FOR EACH ROW] 
BEGIN 
  --trigger code 
END;
/ 

This type of trigger completely replaces the triggering action of the DML statement. This type of trigger is used for views for which DML operations are not allowed.

Therefore, all the three triggers – Before trigger, After trigger and Instead of triggers could be used if needed as a response to changes in the database tables. The use of such triggers could be useful to automate some of the repetitive tasks and maintain the data consistency.