Triggers in PL/SQL

 Triggers in PL/SQL:

·       ·    Triggers are stored programs, which are automatically executed or fired      when some events occur. 

·       ·    Triggers can be defined on the table, view, schema, or database with which the event is associated.

·       ·    Triggers are, written to be executed in response to,

1.    database manipulation (DML) statement(Insert, delete, update)

2.    database definition (DDL) statements(create, alter, drop )  

Uses or Triggers:

·       ·    Triggers are used to avoid the invalid transactions

·       ·    Synchronous replication of tables

·      ·    Generating some derived column values automatically

Syntax for Creating Triggers:

Create [or replace] trigger trigger_name  {Before | After | Instead of}  {Insert [OR] | Update [OR] | Delete}  [Of column_name]  [On table_name]  [Referencing old as o new as n]  [For each row] When (condition) Declare [ Declaration statements] Begin [Executable statements] Exception [Exception handling statements] End;

Syntax Explanation:

·       Create [or replace] trigger trigger_name – Mandantory statement used to Creates or replaces an existing trigger with the Trigger_name

·       {Before | After | Instead of}  - This is for when the trigger will be executed.

·       {Insert [OR] | Update [OR] | Delete} – This specifies the DML operation.

·       [Of column_name]  - This Specifies the column name that only updated.

·       [On table_name]  - Name of the table which is used in the Trigger.

·       [Referencing old as o new as n] -This allows you to refer new and old values for INSERT, UPDATE, and DELETE operations. 

·       [For each row] - This specifies a row-level trigger

·       WHEN (condition) - This provides a condition for rows for which the trigger would fire.

Example:

Following Trigger will fire before insert or update of sal in the emp table:

CREATE OR REPLACE TRIGGER xxdtj_sal_check    BEFORE INSERT OR UPDATE OF sal, comm    ON emp    FOR EACH ROW DECLARE    sal           NUMBER;    comm          NUMBER;    v_exception   EXCEPTION; BEGIN    SELECT   SUM (sal * NVL (comm, 0))        INTO v_sal        FROM emp       WHERE ename = :p_ename    GROUP BY sal;      IF v_sal > 10000    THEN       UPDATE emp          SET sal = v_sal;    END IF;      IF v_sal < 10000    THEN       RAISE v_exception;    END IF; EXCEPTION    WHEN v_exception    THEN       Dbms_output.put_line (   'Please increase the salary of the employee'                             ); END;

Sample output:

ename	sal	Comm
Tani	5000	Null

P_ename =5000

 

The output will be,

Please increase the salary of the employee

 

 

 

Share: