TRIGGER
Tipp 80
Thema: TRIGGER
Datenbank-Version: RDBMS 8.x
Erstellt am 07.10.19
Bearbeitet am 07.10.19
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;
/