231107 PL/SQL SESSION TRIGGER

<SYS SESSION>

CREATE USER green IDENTIFIED BY oracle;
GRANT CREATE SESSION TO green;

CREATE TABLE audit_emp_sal (
    name     VARCHAR2(30),
    day      TIMESTAMP,
    id       NUMBER,
    old_sal  NUMBER,
    new_sal  NUMBER
);

CREATE OR REPLACE PROCEDURE update_proc (
    p_id IN NUMBER ) 
IS
BEGIN
    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = p_id;
END update_proc;
/

SELECT salary
FROM hr.employees
WHERE employee_id = 100;

execute update_proc(100);

GRANT EXECUTE ON hr.update_proc TO green;

SELECT * FROM user_tab_privs;

CREATE OR REPLACE TRIGGER sal_audit
AFTER 
    UPDATE OF salary ON hr.employees
    FOR EACH ROW
WHEN (new.salary != old.salary)
BEGIN
    IF :new.salary != :old.salary THEN
        INSERT INTO audit_emp_sal (NAME, DAY, ID, old_sal, new_sal)
        VALUES (USER, systimestamp, :NEW.employee_id, :OLD.salary, :NEW.salary);
    END IF;
END sal_audit;
/

<green session>

SELECT * FROM user_tab_privs;

execute hr.update_proc(100);
commit;

<hr session>

SELECT * FROM hr.audit_emp_sal;

<green session>

execute hr.update_proc(101);

<hr session>

SELECT * FROM hr.audit_emp_sal;

UPDATE hr.employees
SET salary = salary*1.1
WHERE employee_id =101; <- wditing, no wait(green session 에서 rollback 하는 순간)

-- 생성된 트리거 조회
SELECT * FROM user_triggers;

DROP TRIGGER sal_audit;

SELECT * FROM user_source WHERE name = 'UPDATE_PROC';