Posts Tagged ‘oracle’

PL/SQL Trigger 7 – After Insert Trigger Example

Create an AFTER trigger, which is triggered after updating values of bsal and allw.The trigger should update the TSAL with the latest values of bsal and allw.

Answer

CREATE OR REPLACE TRIGGER au_empp
AFTER UPDATE ON EMPPP_a5518 FOR EACH ROW
declare
    tot number(5);
BEGIN
    
    :OLD.TSAL:=:OLD.BSAL + :OLD.ALLW;
    –:NEW.TSAL:=:NEW.BSAL + :NEW.ALLW;
    
END au_empp;
/

 

Read more »

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 »