如何修复触发器错误?Oracle 19 Express

Question

我想跟踪某个表的用户活动。为此，我创建了一个表，可以在其中插入审计数据：

CREATE TABLE  "AUDIT_FINALPAPERS" 
(    "TBLID" NUMBER, 
"NAME" VARCHAR2(10), 
"REMARKS" VARCHAR2(255), 
"AUDIT_USER" VARCHAR2(50), 
"AUDIT_DATE" DATE, 
"AUDIT_ACTION" VARCHAR2(30)
)

然后创建一个触发器来跟踪用户活动并将数据插入审计表：

CREATE OR REPLACE TRIGGER AUDIT_TBL1
2     -- starts on every update, insert or delete command
3     AFTER INSERT OR DELETE OR UPDATE
4     ON TBL1
5     FOR EACH ROW
6  DECLARE
7     -- variable which declares if update, delete or insert process
8     v_trg_action  VARCHAR2 (30);
9     v_user        VARCHAR2 (30);
10  BEGIN
11     v_user := SYS_CONTEXT ('APEX$SESSION', 'APP_USER');
12
13     IF UPDATING
14     THEN
15        -- when update
16        v_trg_action := 'UPDATED.';
17     ELSIF DELETING
18     THEN
19        -- when delete
20        v_trg_action := 'DELETED.';
21     ELSIF INSERTING
22     THEN
23        -- when insert
24        v_trg_action := 'SUBMITTED.';      -- aKtion
25     ELSE
26        -- if something else
27        v_trg_action := NULL;
28     END IF;
29
30     IF v_trg_action IN ('DELETED.', 'UPDATED.')
31     THEN
32        -- if v_trg_action is DELETE or UPDATE then insert old table values
33        INSERT INTO AUDIT_TBL1 (TBLID,
34                                NAME,
35                                REMARKS,
36                                AUDIT_USER,
37                                AUDIT_DATE,
38                                AUDIT_ACTION)
39             VALUES (:OLD.TBLID,
40                     :OLD.NAME,
41                     :OLD.REMARKS,
42                     v_user,
43                     SYSDATE,
44                     v_trg_action);
45     ELSE
46        INSERT INTO AUDIT_TBL1 (TBLID,
47                                NAME,
48                                REMARKS,
49                                AUDIT_USER,
50                                AUDIT_DATE,
51                                AUDIT_ACTION)
52             VALUES (:NEW.TBLID,
53                     :NEW.NAME,
54                     :NEW.REMARKS,
55                     v_user,
56                     SYSDATE,
57                     v_trg_action);
58     END IF;
59  -- about the insert command on the audit table
60  -- for current apex user:SYS_CONTEXT('APEX$SESSION','APP_USER');
61  -- for date: SYSDATE
62  -- for sql command: v_trg_action
63  END AUDIT_TBL1;
64  /

然而，我得到错误：PLS-00049：错误的绑定变量这究竟意味着什么？如何解决？

Answer 1

看起来是错误报告的错误。当我运行您的代码时，它运行得很好，除了对于这一部分，因为您将变量命名为v_trg_aKtion(而不是aCtion(

21     ELSIF INSERTING
22     THEN
23        -- when insert
24        v_trg_action := 'SUBMITTED.';      -- aKtion

看一看；触发器已创建。

SQL> CREATE OR REPLACE TRIGGER AUDIT_TBL1
2     -- starts on every update, insert or delete command
3     AFTER INSERT OR DELETE OR UPDATE
4     ON TBL1
5     FOR EACH ROW
6  DECLARE
7     -- variable which declares if update, delete or insert process
8     v_trg_action  VARCHAR2 (30);
9     v_user        VARCHAR2 (30);
10  BEGIN
11     v_user := SYS_CONTEXT ('APEX$SESSION', 'APP_USER');
12
13     IF UPDATING
14     THEN
15        -- when update
16        v_trg_action := 'UPDATED.';
17     ELSIF DELETING
18     THEN
19        -- when delete
20        v_trg_action := 'DELETED.';
21     ELSIF INSERTING
22     THEN
23        -- when insert
24        v_trg_action := 'SUBMITTED.';      -- aKtion
25     ELSE
26        -- if something else
27        v_trg_action := NULL;
28     END IF;
29
30     IF v_trg_action IN ('DELETED.', 'UPDATED.')
31     THEN
32        -- if v_trg_action is DELETE or UPDATE then insert old table values
33        INSERT INTO AUDIT_TBL1 (TBLID,
34                                NAME,
35                                REMARKS,
36                                AUDIT_USER,
37                                AUDIT_DATE,
38                                AUDIT_ACTION)
39             VALUES (:OLD.TBLID,
40                     :OLD.NAME,
41                     :OLD.REMARKS,
42                     v_user,
43                     SYSDATE,
44                     v_trg_action);
45     ELSE
46        INSERT INTO AUDIT_TBL1 (TBLID,
47                                NAME,
48                                REMARKS,
49                                AUDIT_USER,
50                                AUDIT_DATE,
51                                AUDIT_ACTION)
52             VALUES (:NEW.TBLID,
53                     :NEW.NAME,
54                     :NEW.REMARKS,
55                     v_user,
56                     SYSDATE,
57                     v_trg_action);
58     END IF;
59  -- about the insert command on the audit table
60  -- for current apex user:SYS_CONTEXT('APEX$SESSION','APP_USER');
61  -- for date: SYSDATE
62  -- for sql command: v_trg_action
63  END AUDIT_TBL1;
64  /
Trigger created.
SQL>