DROP TABLE scott.emp_trig;
CREATE TABLE scott.emp_trig
AS
SELECT
*
FROM
scott.emp;
col cmode format A6 col user_name format a10 col sal format 9999
DROP TABLE scott.emp_audit;
CREATE TABLE scott.emp_audit
AS
SELECT
*
FROM
scott.emp
WHERE
1 = 2;
ALTER TABLE scott.emp_audit ADD (
old_sal NUMBER(7, 2),
new_sal NUMBER(7, 2),
user_name VARCHAR2(30),
change_date DATE,
event CHAR(1)
);
CREATE OR REPLACE TRIGGER scott.emp_trig_idu BEFORE
INSERT OR UPDATE OR DELETE ON scott.emp_trig
FOR EACH ROW
DECLARE
v_event CHAR(1) := 'X';
BEGIN
IF inserting THEN
v_event := 'I';
ELSIF updating THEN
v_event := 'U';
ELSE --DELETING
v_event := 'D';
END IF;
INSERT INTO scott.emp_audit (
empno,
old_sal,
new_sal,
user_name,
change_date,
event
) VALUES (
:old.empno,
:old.sal,
:new.sal,
user,
sysdate,
v_event
);
END;
/