Triggers in PL/SQL | BEFORE Triggers | AFTER Triggers | Row-Level Triggers | Statement-Level Triggers | Instead of Triggers | PL/SQL

 

Triggers

 

A collection of instructions that are automatically carried out (or "triggered") in reaction to specified occurrences on a given table or view is known as a trigger. 

 

Triggers are used in databases to maintain data integrity, carry out extra tasks, and enforce business rules. 

 

They can be programmed to run before or after particular events, such INSERT, UPDATE, and DELETE.

 

          Here is a general definition of triggers in PL/SQL:

 

Trigger Definition: 

         A trigger is a named PL/SQL block or stored procedure that is automatically              executed (or "triggered") in response to a specific event on a specified table or view.

 

Important Features of Triggers: -

 Event: - A trigger is linked to a particular database action, like INSERT, UPDATE, or DELETE.

 Timing: - Triggers can be categorized according to when they are executed:

 BEFORE Triggers: Carried out prior to the activation incident. used to amend or verify      data before making changes to it. 

 AFTER Triggers: Executed after the triggering event. Used for actions that should occur       after the data has  been changed.  

 Range: Triggers can be classified on several levels:

 Row-Level Triggers: One action is taken for every impacted row. 

 Statement-Level Triggers: Regardless of the number of rows impacted, just one execution of the full triggering statement is required.

 

Basic Syntax of Triggers: -

Create or replace trigger trigger_name

{Before | After| Instead of} -- Timing

{Insert| Update | Delete | {Insert or Update} | {Upadate or Insert}} -- Event

[{Of column_name | On table_name |

{For each row | For each statement}}] -- Range

[When (condition)] -- Condition (Optional)

Is

Begin

    -- Trigger logic

    -- PL/SQL statements

End[trigger_name];

/

 

Row level TRIGGER Example: -

Row Level Triggers require the use of the "for each row" clause in the trigger specification because the trigger body is run for each and every row for each DML statement.

DML transaction values are internally and automatically stored in two rollback segment qualifiers in Oracle whenever we use a row level trigger.

These are

·        :OLD

·        :NEW

 

Example: -

 Create or replace trigger delete_employee_trigger

 Before Delete On employees

 For Each Row

 Begin

    -- Insert deleted employee information into the audit log

Insert into employee_audit_log (
    log_id,
    employee_id,
    employee_name,
    deletion_date
) Values (
    employee_audit_log_seq.nextval,
    :old.employee_id,
    :old.employee_name,
    sysdate
);   

    DBMS_OUTPUT.PUT_LINE ('Employee ' || :OLD.employee_name || ' has been deleted.');

End;

 

 The applications listed below utilize row level triggers in Oracle. These are...

  Putting business rules into action

  Examining some columns

  Automatic rise

 

Statement Level Triggers Example: -

• The trigger body in statement level triggers is only run once for each DML statement. Oracle statement level triggers do not have the ":old" or ":new" qualifiers, and statement level triggers do not contain the "for each row" phrase.

• Statement level triggers are typically required for creating time component-based applications through the usage of triggers. Statement level trigger performance is often much higher than row level trigger performance in all relational databases.

 

Example: -

 

Create or replace trigger before_insert_statement_example

Before Insert On employees

Declare

    v_total_sal NUMBER:= 0;

Begin

    Select NVL (SUM (salary), 0) 

    Into v_total_sal  

    From employees;

    Dbms_output.put_line ('Total Salary before INSERT: ' || v_total_sal);

End before_insert_statement_example;

/ 

 

Instead of Triggers: -

 

Oracle 8i introduced instead of trigger. Instead of trigger are created on view by default instead of trigger are row level trigger.

 In general, DML processes cannot be completed using complex views on base tables. Oracle 8i introduced instead of trigger in PL/SQL to solve this issue.

When we create an instead of a trigger on complex view, then only we are permitted to do DML operations through the complex view to the base table.

For this reason, instead of trigger convert non-updatable views become updatable views.

 

Syntax:

 

Create or replace trigger Triggername

Instead Of Insert/Update/Delete on viewname

For  Each Row

Declare

……………….

……………….

Begin

……………….

……………….

End;

 

Example 1: -

 

Create or replace trigger tr2

Instead Of insert on v1

For Each Row

Begin

               Insert into test1 (name) Values (:new.name);

               Insert into test2 (sub) Values (:new.sub);

End;

 

Example 2: -

 

Create or replace trigger instead_of_orders_trigger

Instead Of Insert or Update or Delete On orders_v

For Each Row

Declare

    v_action Varchar2(10);

Begin

    -- Determine the action type

    If Inserting Then

        v_action := 'Insert';

    Elsif Updating Then

        v_action := 'Update';

    Elsif Deleting Then

        v_action := 'Delete';

    End If;

 

    Insert Into order_audit_log (audit_id, action, action_date, total_orders)

    Values (audit_id_seq.nextval, v_action, sysdate, (Select COUNT(*) From orders));

 

     Insert Into orders  Values(100, sysdate, 1000, 10000);

    -- You can also perform other actions based on the type of operation

 

    -- Example: Allow the original operation to proceed for Insert and Update

    If Inserting Or Updating Then

        -- You may want to perform additional actions here

        Null;

        Dbms_output.put_line('Hi');

    End If;

 

    -- Example: Prevent DELETE operation

    If Deleting Then

       -- Dbms_output.put_line('delete');

        raise_application_error(-20000, 'DELETE operation is not allowed.');

    End If;

End;