BEFORE Trigger - (Oracle Advance)

A BEFORE trigger in Oracle is a database object that is invoked automatically before an INSERT, UPDATE, or DELETE operation is performed on a table. It can be used to modify data values before they are stored in the table, enforce business rules, or perform other checks and validations.

Syntax

CREATE [OR REPLACE] TRIGGER trigger_name 
BEFORE INSERT OR UPDATE OR DELETE
ON table_name
[FOR EACH ROW]
BEGIN
    -- Trigger code goes here
END;

Here, trigger_name is the name of the trigger, table_name is the name of the table, and the BEFORE INSERT OR UPDATE OR DELETE clause specifies the type of event that will trigger the execution of the trigger. The FOR EACH ROW clause specifies that the trigger will be executed once for each row affected by the operation.

Example

Here is an example of a BEFORE trigger that prevents the insertion of a record into a table if it contains a duplicate value in a specific column:

CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
    IF EXISTS (SELECT 1 FROM my_table WHERE column_name = :new.column_name) THEN
        RAISE_APPLICATION_ERROR(-20001, 'Duplicate value found');
    END IF;
END;

Explanation

In the above example, we have created a trigger called my_trigger on a table called my_table. The trigger is defined as a BEFORE INSERT trigger that is executed once for each row affected by the INSERT operation. The trigger code uses a SELECT statement to check if a record already exists in the table with the same value in the column_name column as the record being inserted. If a duplicate value is found, the trigger raises an error using the RAISE_APPLICATION_ERROR procedure.

Use

BEFORE triggers can be used to enforce business rules and data validation constraints, as well as to modify data values before they are stored in the table. They are useful for ensuring data integrity and consistency in the database.

Important Points

• A BEFORE trigger is a database object that is invoked automatically before an INSERT, UPDATE, or DELETE operation is performed on a table.
• BEFORE triggers can be used to modify data values before they are stored in the table, enforce business rules, or perform other checks and validations.
• The FOR EACH ROW clause specifies that the trigger will be executed once for each row affected by the operation.

Summary

In summary, a BEFORE trigger in Oracle is a useful database object that can be used to enforce business rules, perform data validation checks, and modify data values before they are stored in a table. The BEFORE INSERT OR UPDATE OR DELETE clause specifies the type of event that triggers the trigger, and the FOR EACH ROW clause specifies that the trigger will execute for each row affected by the operation.