Trigger - (Oracle Advance)

A trigger in Oracle is a set of PL/SQL statements that are automatically executed when a certain event occurs, such as the insertion, modification, or deletion of data from a table. Triggers are used to enforce business rules and data validation, automate database activities, audit changes made to data, and perform complex data transformations.

Syntax

The syntax for creating a trigger in Oracle is as follows:

CREATE OR REPLACE TRIGGER trigger_name
trigger_time trigger_event ON table_name
[FOR EACH ROW]
DECLARE
    -- variable declarations
BEGIN
    -- trigger logic
END;

Here, trigger_name is the name of the trigger, trigger_time is the time at which the trigger is executed (BEFORE or AFTER), trigger_event is the event that fires the trigger (INSERT, UPDATE, or DELETE), table_name is the name of the table on which the trigger is created, and [FOR EACH ROW] specifies that the trigger is fired for each row affected by the event.

Example

Here is an example of a BEFORE INSERT trigger that sets the hire date of an employee to the current date:

CREATE OR REPLACE TRIGGER set_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.hire_date := SYSDATE;
END;

The :NEW keyword refers to the new row being inserted into the table, and SYSDATE is a built-in Oracle function that returns the current date and time.

Output

When a new row is inserted into the employees table, the set_hire_date trigger is automatically executed, and the hire_date field of the new row is set to the current date and time.

Explanation

In the above example, we have created a BEFORE INSERT trigger named set_hire_date that is automatically executed when a new row is inserted into the employees table. The trigger sets the hire_date field of the new row to the current date and time using the SYSDATE function.

Use

Triggers are used in Oracle to enforce business rules and data validation, automate database activities, audit changes made to data, and perform complex data transformations. They are a powerful tool that can help improve the consistency and accuracy of data within a database.

Important Points

• A trigger in Oracle is a set of PL/SQL statements that are automatically executed when a certain event occurs.
• Triggers can be used to enforce business rules and data validation, automate database activities, audit changes made to data, and perform complex data transformations.
• Triggers are created using the CREATE TRIGGER statement and can be set to execute BEFORE or AFTER an event.

Summary

In summary, triggers in Oracle are a powerful tool for enforcing business rules, validating data, automating database activities, and performing complex data transformations. They are created using the CREATE TRIGGER statement and can be set to execute BEFORE or AFTER a specific event, such as the insertion, modification, or deletion of data from a table. Triggers help improve the consistency and accuracy of data within a database and are an essential part of Oracle database programming.