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;
/
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;
/
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;
/
April 10th, 2013 
