Jijo K Jose

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;
/
 

INSERT INTO EMPPP_a5518 (EMPNO, NAME, BSAL, ALLW) VALUES (3,'jijo',45,67);