sql的简单触发器/过程问题

brjng4g3  于 2021-08-13  发布在  Java
关注(0)|答案(2)|浏览(393)

我试图在oracleapexsql中运行这个审计跟踪触发器,但是我不断地得到相同的错误,我不知道我做错了什么。我还需要对数据库中的每个表执行相同的触发器。。。有没有一种方法可以让我通过一个程序来做同样的事情,只需要做一次?

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  -- starts on every update, insert or delete command
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
  -- variable which declares if update, delete or insert process
  v_trg_action varchar2(10);
BEGIN
  IF updating  THEN
    -- when update
    v_trg_action := 'UPDATE';
  ELSIF deleting  THEN
    -- when delete
    v_trg_action := 'DELETE';
  ELSIF inserting  THEN
    -- when insert
    v_trg_action := 'INSERT';
  ELSE
    -- if something else
  END IF;
  IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
      -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
   INSERT INTO AUDIT_TRAIL
  ( AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  ELSE
  END IF;
  -- about the insert command on the audit table
  -- for current apex user: v('APP_USER')
  -- for date: SYSDATE
  -- for sql command: v_trg_action   
END AUDIT_TRAIL_USERS_TRIG;

我得到的错误(我肯定我有比它对我说的更多)如下:
编译失败,第16行(03:29:53)与编译错误相关联的行号与第一个begin语句有关。这只会影响数据库触发器的编译。
pls-00103:遇到符号“end”,要求出现以下情况之一:(begin case declare exit for goto if loop mod null pragma raise return select update while with<<continue close current delete fetch lock insert open rollback savepoint set sql execute commit for all merge pipe purge json\u exists json\u value json\u query json\u objectjson_数组编译失败,第25行(03:29:53)与编译错误相关联的行号与第一个begin语句有关。这只会影响数据库触发器的编译。
pls-00103:遇到符号“end”,要求出现以下情况之一:(begin case declare exit for goto if loop mod null pragma raise return select update while with<<continue close current delete fetch lock insert open rollback savepoint set sql execute commit for all merge pipe purge json\u exists json\u value json\u query json\u objectjson\u数组

wwodge7n

wwodge7n1#

if..else块不能为空。如果你不需要他们移除它,我添加了一个假人 NULL 调用代码进行编译。根据需要添加适当的逻辑,否则删除该块。

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  -- starts on every update, insert or delete command
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
  -- variable which declares if update, delete or insert process
  v_trg_action varchar2(10);
BEGIN
  IF updating  THEN
    -- when update
    v_trg_action := 'UPDATE';
  ELSIF deleting  THEN
    -- when delete
    v_trg_action := 'DELETE';
  ELSIF inserting  THEN
    -- when insert
    v_trg_action := 'INSERT';
  ELSE
    -- if something else
    NULL;
  END IF;
  IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
      -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
   INSERT INTO AUDIT_TRAIL
  ( AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
   VALUES
  (UPPER(v('APP_USER')), SYSDATE, v_trg_action);

  null;
  ELSE
   NULL;
  END IF;
  -- about the insert command on the audit table
  -- for current apex user: v('APP_USER')
  -- for date: SYSDATE
  -- for sql command: v_trg_action   
END AUDIT_TRAIL_USERS_TRIG;
llycmphe

llycmphe2#

使用此代码,它的工作方式与您的相同

create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  AFTER INSERT OR DELETE OR UPDATE ON USERS
  FOR EACH ROW
DECLARE
BEGIN
    IF inserting or updating or deleting  THEN
        INSERT INTO AUDIT_TRAIL
            (AUDIT_USER,  AUDIT_DATE,  AUDIT_ACTION)
        VALUES
            (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
    END IF;  
END AUDIT_TRAIL_USERS_TRIG;

相关问题