SQL SCHEMA :-

-- CREATE TABLE emp_1( -- emp_num int, -- emp_lname varchar2(50), -- emp_fname varchar2(50), -- emp_initial varchar2(50), -- emp_hiredate date, -- emp_jobcode int -- );

-- CREATE TABLE locked( -- user_name varchar2(50), -- user_sysdate date, -- usr_action varchar2(50) -- );

-- CREATE TABLE old_values( -- emp_num int, -- emp_lname varchar2(50), -- emp_fname varchar2(50), -- emp_initial varchar2(50), -- emp_hiredate date, -- emp_jobcode int -- );

ERROR :-

ORA-04098: trigger 'SQL_QLRDZMMOTYBAYOWSTJVKINICK.DML_TRIGGER' is invalid and failed re-validation

QUESTION :-

Given a table create a trigger that such that any DML operation performed will lead to storing of username,sysdate and ANY DML action performed, to be stored in a table called locked with attributes(user_name,user_sysdate,usr_action) and also old values should be stored in a separate table.

SQL TRIGGER FOR QUESTION 1 CREATE OR REPLACE TRIGGER emp_1_trg AFTER INSERT OR UPDATE OR DELETE ON emp_1 FOR EACH ROW DECLARE V_user_name VARCHAR2(50); V_sysdate DATE; V_action VARCHAR2(50); BEGIN v_user_name := user; v_sysdate := sysdate; v_action := case when inserting then 'INSERT' when updating then 'UPDATE' when deleting then 'DELETE' end; - Insert record into "locked" table INSERT INTO locked (user_name, user_sysdate, usr_action) VALUES (v_user_name, v_sysdate, v_action); -- Insert old values into "old_values" table IF UPDATING OR DELETING THEN INSERT INT0 old_values (emp_num, emp_lname, emp_fname, emp_initial, emp_hiredate, emp_jobcode) VALUES(:old. emp_num, :old. emp_lname, :old.emp_fname, :old.emp_initial, :old.emp_hiredate, :old. emp_jobcode); END IF; END; / -- DELETE FROM emp_1 WHERE emp_jobcode = '402