Archive for April, 2013

PL/SQL Trigger 9 – Trigger Having Conditions Example

Create trigger which prevent insertion/updation of EMPNO in EMP_<employee_id> table (Triggered only if inserting empno is greater than 1000)

Answer

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

 

Read more »

PL/SQL Trigger 8 – Row Level Trigger Example

Create a statement trigger, which will audit who all are inserting and deleting values in EMP_<employeeid> table to Audit table given above.

Answer

CREATE OR REPLACE TRIGGER audit_empp
AFTER INSERT OR DELETE ON EMPPP_a5518
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
declare
    name1 varchar2(15):='';
BEGIN
    
    CASE
    WHEN INSERTING THEN
        name1:=:n.name;
        insert into audit_a5518(DB_USER, UPDATED_ON) values (name1,sysdate);
    WHEN DELETING THEN
        name1:=:o.name;
        insert into audit_a5518(DB_USER, UPDATED_ON) values (name1,sysdate);
    END CASE;

END au_empp;
/

 

Read more »

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 »