Archive for April, 2013

PL/SQL Trigger 6 – Before Insert Trigger Example

Create a Table EMP_<employee_id> with following fields
        EMPNO NUMBER
        NAME    VARCHAR2
        BSAL NUMBER
        ALLW NUMBER
        TSAL   NUMBER


Create an Audit Table AUDIT_EMP_<employee_id> with following fields
       DB_USER    VARCHAR2
       UPDATED_ON   DATE

 

Create a BEFORE trigger which is triggered before inserting values into EMP table and which updates the TSAL as sum of bsal and allw

Answer

CREATE OR REPLACE TRIGGER bf_empp
BEFORE INSERT ON EMPPP_a5518 FOR EACH ROW
declare
    tot number(5);
BEGIN
    
    :NEW.TSAL:=:NEW.BSAL + :NEW.ALLW;
    
END bf_empp;
/

 

Read more »

PL/SQL Trigger 5 – DROP, DISABLE and ENABLE Trigger

Drop a Trigger

The syntax for a dropping a Trigger is:

DROP TRIGGER trigger_name;

Disable a Trigger

The syntax for a disabling a Trigger is:

ALTER TRIGGER trigger_name DISABLE;

Disable all Triggers on a table

The syntax for a disabling all Triggers on a table is:

ALTER TABLE table_name DISABLE ALL TRIGGERS;

Enable a Trigger

The syntax for a enabling a Trigger is:

ALTER TRIGGER trigger_name ENABLE;

Read more »

PL/SQL Trigger 4 – BEFORE and AFTER DELETE Trigger

BEFORE DELETE Trigger

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.

The syntax for an BEFORE DELETE Trigger is:

CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
   ON table_name
   [ FOR EACH ROW ]

DECLARE
   — variable declarations

BEGIN
   — trigger code

EXCEPTION
   WHEN …
   — exception handling

END;

Restrictions

    * You can not create a BEFORE trigger on a view.
    * You can update the :NEW values.
    * You can not update the :OLD values.

 

Read more »