Mastering SQL for Data Science: A Comprehensive Guide Day-13

Mastering SQL for Data Science: A Comprehensive Guide 

Day-13

Trigger

v Trigger defines an action the database should take when some database related event occurs. Trigger may be used to supplement declarative referential integrity, to enforce complex business rules, or to audit changes to data. The code within a trigger, called a trigger body, is made up of PL/SQL blocks.

The execution of triggers is also transparent to the user. Triggers are also executed by the database when specific types of data manipulation commands are performed on specific tables.

In other words, a trigger is a PL/SQL block that is associated the table, stored in a table and executed in response to a specific data manipulation event. Triggers can be executed or fired in response to the following events;

ü  A row inserted into a table

ü  A row updated into a table

ü  A row deleted from a table

It is not possible to define a trigger to fire when a row is selected.

 

Ø  Types of triggers:

A trigger’s type is defined by the type of triggering transaction and by the level at which the trigger is executed.

1)   Row level Trigger:

Row level triggers execute once for each row in a transaction. Row level triggers are the most common type of trigger, they are often used in a data auditing application.

2)   Statement level Trigger:

Statement level triggers execute once for each transaction. For example, if a single transaction inserted 500 rows into a table, then a statement level trigger on that table would only be executed once.

3)   Before and after Trigger:

If you need to set a column value in an inserted row via your trigger, then you need to use a before insert trigger to access the new values, using an after insert trigger would not allow you to set the inserted value, since the row will already have been inserted into the table.

4)   Instead of Trigger:

You can use of instead of trigger to tell oracle what to do instead performing the actions that invoked the trigger, For example, you would use an trigger on a view to redirect inserts into table or to update multiple tables that are part of a view.

 Ø  Use of Triggers:

ü  Enforcing business rules

ü  Maintaining referential integrity

ü  Enforcing security

ü  Maintaining a historical log of changes

ü  Generating column values, including primary key values

ü  Replicating data

Syntax:

    Create or replace trigger <trigger_name>

    [before|after|instead of ]

    [insert|update|delete]

    On <table/view>

    For each [row|statement]

     When <condition>

    PL/SQL block;

 E.g: -- create a trigger to convert name into upper case when user insert or update any record within d_emp table

     Create or replace trigger t1

    Before insert or update

    On d_emp

    For each row

     Begin

          :new.name:=upper(:new.name);

    End;

    /

 E.g: --create a trigger that is fired when an update or delete operation is performed on the d_emp table. The trigger first checks for the operations being performed on the table, then depending on the operation being performed, a variable assigned the value update or delete. Previous values modified by the user, will insert into the emp_back table.

Create or replace trigger tri1

After update or delete

On d_emp

For each row

Declare

          Op varchar2(10);

Begin

          if updating then

                   op:=’update’;

          end if;

           if deleting then

                   op:=’delete’;

          end if;

          insert into emp_back values

          (:old.emp_no,:old.ename,:old.sal,op,sysdate);

    End;

    /

 --Instead of Trigger

    When user will create a complex/Readable view, at that time user can’t insert any new record within a readable view, because it affects multiple tables at a time. So, user can use instead of trigger to insert a new record like;

    E.g:

    create or replace trigger view_join

    instead of insert on empview

    for each row

    begin

      insert into dept(deptno,dname,location) 

      values (:new,depnto,:new.dname,:new.location);

insert into d_emp1(empno,ename,job,sal,deptno) values(:new.empno,:new.ename,:new.job,:new.sal,:new.deptno);

    end;

    /