Types of Data base triggers? What is a Trigger in Oracle

What is a trigger? A database trigger is a stored procedure that is fired when an insert, update or delete statement is issued against the associated table.  Data base triggers can be used for the following purposes:

1. To generate data automatically [update or insert]
2. To enforce complex integrity constraints. 
3. To customize complex security authorizations. [Unauthorized or inadvertent delete, or insert or update]
4. To maintain replicate tables [DML statements into copy of tables also when an insert is made in the original]
5. To audit data modification.
   

Required system privileges:To create a trigger on a table, one should have the following privileges:

1. One must either own the table or have the ALTER privilege for the table, or ALTER ANY TABLE system privilege.
2. One must have the CREATE TRIGGER system privilege, to create triggers in another user’s account.
3. One must have CREATE ANY TRIGGER system privilege.  These privileges are part of the RESOURCE role.
4. To alter a trigger, one must have ALTER ANY TRIGGER system privilege.
5. To create a trigger on a database–level-event, one must have the ADMINISTER DATABASE TRIGGER system privilege.  This is for the DBA.

Note:  Triggers may reference tables other than the one that initiated the triggering event.  Ex:  If there is trigger for auditing updates in a table A, and based on the updates one may be inserting a record in another table B.  In such situations we must have privileges for inserting in table B also.

Parts of a trigger:  A database trigger has three parts namely

A trigger statement

A trigger body and

A trigger restriction

The Trigger statement specifies the DML statements like update, delete, and insert, and it fires the trigger body.  It also specifies the table to which the trigger is associated.

The Trigger body is a PL/SQL block that is executed when a triggering statement is issued. 

The Trigger restrictions The restrictions can be achieved using the WHEN clause as shown in the syntax.  They can be included in the definition of a row trigger, where in, the condition in the WHEN clause is evaluated for each row that is affected by the trigger. [Which can be insert or update or delete mentioned in the trigger statement].  A sub query cannot be included in the WHEN clause.

Types of trigger:  [categorized based on when they are fired]

1. Before, 2.  After, 3. For each row and 4. For each statement [default].

1.      Before/After:  This option can be used to specify when the trigger body should be fired with respect to the triggering statement.  If the user includes a before option, then Oracle fires the trigger (trigger body) before executing the triggering statement [insert or update or delete].

If the user includes the after option, then Oracle fires the trigger (trigger body) after executing the trigger statement.

2.      Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the trigger action is to be executed:

1. Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
2. Once for the triggering statement, no matter how many rows it affects

Row Triggers:

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not executed.

Statement Triggers :

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected.   For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Data base triggers can be created only on the tables, but the triggers created are also applicable for the views based on those table.

Any statement that causes the trigger to be fired is known as triggering statement. 

Ex1: The default statement level trigger

set serveroutput on

create table my_emp25 as select empno,ename,sal,job,deptno from emp where 1=2;

Table created.

 create or replace trigger my_emp25_mani

 before insert on my_emp25

 begin

 dbms_output.put_line('Inserting records');

 end;

    /

Trigger created.

insert into my_emp25 values (9999,'amir',2344.67,'actor',40);

Inserting records

1 row created.

select * from my_emp25;

     EMPNO ENAME             SAL JOB           DEPTNO

---------- ---------- ---------- --------- ----------

      9999 amir          2344.67 actor             40