PLSQL 自律型TRANSACTION 与 TRIGGER

来源：互联网发布：知乎如何删除关注话题编辑：程序博客网时间：2024/06/03 01:16

CREATE TABLE test02 (
a NUMBER,
b VARCHAR2(10)
);

ALTER TABLE test02 ADD CONSTRAINT check_test02 CHECK (
a > 10
);

CREATE TABLE audit01 (
id NUMBER,
msg VARCHAR2(200)
);

CREATE SEQUENCE seq_audit;

create or replace trigger trig01
before update on test02
for each row
declare
msg_string varchar2(200);
proceducure proc_audit_insert(p_msg in varchar2)
is
pragma autonomous_transaction;
begin
insert into audit01 values (seq_audit.nextval,p_msg);
commit;
end proc_audit_insert;

begin
msg_string := to_char(sysdate,'YY/MM/DD HH24:MI:SS') ||
'にユーザ' || USER|| 'が、' || 'A列の値を ' ||
:OLD.A || '→' || :NEW.A || ', B列の値を ' ||
:OLD.B || '→' || :NEW.B || ' に更新';
proc_audit_insert(msg_string);
end trig01;
/

INSERT INTO TEST01(A,B) VALUES (11,'ABC');
INSERT INTO TEST01(A,B) VALUES (12,'DEF');
COMMIT;
UPDATE TEST01 SET A = 21 WHERE A = 11;
SELECT * FROM TEST01;
SELECT MSG FROM AUDIT01 ORDER BY ID;
ROLLBACK;
SELECT * FROM TEST01;
SELECT MSG FROM AUDIT01 ORDER BY ID;

UPDATE TEST01 SET A = 0 WHERE A = 12;
ELECT * FROM TEST01;
SELECT MSG FROM AUDIT01 ORDER BY ID;

0 0