问如何使用:NEW或:OLD运算符获取oracle触发器中的所有新值。like (:NEW.*)
EN

Stack Overflow用户

提问于 2019-05-20 14:40:29

回答 1查看 774关注 0票数 0

在将触发器插入到另一个表之前，我正在尝试获取触发器中的所有新值和旧值，但我只能获取特定值，而不是所有数据。

我尝试过:NEW.*和:OLD.*它适用于postgress，但不适用于oracle。

CREATE OR REPLACE TRIGGER trg_customer_tbl_ib01
BEFORE INSERT OR UPDATE OR DELETE ON TEST.customer_test
FOR EACH ROW
DECLARE
vAction VARCHAR2(4000) := null;
vFound INT := null;
vREC TEST.CUSTOMER_TEST%ROWTYPE;
BEGIN
IF INSERTING OR UPDATING THEN
select :NEW.* into vREC FROM DUAL;
ELSIF DELETING THEN
select :OLD.* into vREC FROM DUAL;
END IF;
IF DELETING THEN
INSERT INTO TEST.CUSTOMER_TEST_AUDIT SELECT 
CUSTOMER_TEST_AUDIT_id_seq.NEXTVAL,'D',SYSDATE, vREC.* FROM  DUAL;
ELSIF UPDATING THEN
INSERT INTO TEST.CUSTOMER_TEST_AUDIT SELECT 
CUSTOMER_TEST_AUDIT_id_seq.NEXTVAL,'U',SYSDATE, vREC.*FROM  DUAL;
ELSIF INSERTING THEN
INSERT INTO TEST.CUSTOMER_TEST_AUDIT SELECT 
CUSTOMER_TEST_AUDIT_id_seq.NEXTVAL,'I',SYSDATE, vREC.* FROM  DUAL;
END IF;
END;

我收到错误: Error(10,8)：PLS-00049:错误的绑定变量'NEW.‘’

oracle

triggers

oracle12c

回答 1

Stack Overflow用户

发布于 2019-05-20 19:45:20

好吧，别着急。不要使用快捷方式。

测试用例：

SQL> create table test as select * From dept;

Table created.

SQL> create table test_log as select * From dept where 1 = 2;

Table created.

SQL> alter table test_log add (id number, action varchar2(1), datum date);

Table altered.

SQL> create sequence seqlog;

Sequence created.

SQL>

触发器。单独引用每一列。:NEW.*在Postgres中工作的事实并不意味着它在Oracle中也能工作。

SQL> create or replace trigger trg_test
  2    before insert or update or delete on test
  3    for each row
  4  begin
  5    if updating then
  6       insert into test_log (deptno, dname, loc, id, action, datum)
  7         values
  8         (:old.deptno, :old.dname, :old.loc, seqlog.nextval, 'U', sysdate);
  9       insert into test_log (deptno, dname, loc, id, action, datum)
 10         values
 11         (:new.deptno, :new.dname, :new.loc, seqlog.nextval, 'U', sysdate);
 12    elsif inserting then
 13       insert into test_log (deptno, dname, loc, id, action, datum)
 14         values
 15         (:new.deptno, :new.dname, :new.loc, seqlog.nextval, 'I', sysdate);
 16    elsif deleting then
 17       insert into test_log (deptno, dname, loc, id, action, datum)
 18         values
 19         (:old.deptno, :old.dname, :old.loc, seqlog.nextval, 'D', sysdate);
 20    end if;
 21  end;
 22  /

Trigger created.

SQL>

测试：

SQL> alter session set nls_Date_format = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> update test set loc = 'Croatia' where deptno = 10;

1 row updated.

SQL> delete from test where deptno = 20;

1 row deleted.

SQL> insert into test (deptno, dname, loc) values (99, 'IT', 'Zagreb');

1 row created.

SQL> select * From test_log order by id;

    DEPTNO DNAME          LOC                   ID A DATUM
---------- -------------- ------------- ---------- - -------------------
        10 ACCOUNTING     NEW YORK               5 U 20.05.2019 21:44:33
        10 ACCOUNTING     Croatia                6 U 20.05.2019 21:44:33
        20 RESEARCH       DALLAS                 7 D 20.05.2019 21:44:43
        99 IT             Zagreb                 8 I 20.05.2019 21:44:52

SQL>