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

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

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

  1. create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  2. -- starts on every update, insert or delete command
  3. AFTER INSERT OR DELETE OR UPDATE ON USERS
  4. FOR EACH ROW
  5. DECLARE
  6. -- variable which declares if update, delete or insert process
  7. v_trg_action varchar2(10);
  8. BEGIN
  9. IF updating THEN
  10. -- when update
  11. v_trg_action := 'UPDATE';
  12. ELSIF deleting THEN
  13. -- when delete
  14. v_trg_action := 'DELETE';
  15. ELSIF inserting THEN
  16. -- when insert
  17. v_trg_action := 'INSERT';
  18. ELSE
  19. -- if something else
  20. END IF;
  21. IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
  22. -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
  23. INSERT INTO AUDIT_TRAIL
  24. ( AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
  25. VALUES
  26. (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  27. ELSE
  28. END IF;
  29. -- about the insert command on the audit table
  30. -- for current apex user: v('APP_USER')
  31. -- for date: SYSDATE
  32. -- for sql command: v_trg_action
  33. 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 调用代码进行编译。根据需要添加适当的逻辑,否则删除该块。

  1. create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  2. -- starts on every update, insert or delete command
  3. AFTER INSERT OR DELETE OR UPDATE ON USERS
  4. FOR EACH ROW
  5. DECLARE
  6. -- variable which declares if update, delete or insert process
  7. v_trg_action varchar2(10);
  8. BEGIN
  9. IF updating THEN
  10. -- when update
  11. v_trg_action := 'UPDATE';
  12. ELSIF deleting THEN
  13. -- when delete
  14. v_trg_action := 'DELETE';
  15. ELSIF inserting THEN
  16. -- when insert
  17. v_trg_action := 'INSERT';
  18. ELSE
  19. -- if something else
  20. NULL;
  21. END IF;
  22. IF v_trg_action IN ('DELETE','UPDATE','INSERT') THEN
  23. -- if v_trg_action is DELETE, UPDATE OR INSERT then insert old table values
  24. INSERT INTO AUDIT_TRAIL
  25. ( AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
  26. VALUES
  27. (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  28. null;
  29. ELSE
  30. NULL;
  31. END IF;
  32. -- about the insert command on the audit table
  33. -- for current apex user: v('APP_USER')
  34. -- for date: SYSDATE
  35. -- for sql command: v_trg_action
  36. END AUDIT_TRAIL_USERS_TRIG;
展开查看全部
llycmphe

llycmphe2#

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

  1. create or replace TRIGGER AUDIT_TRAIL_USERS_TRIG
  2. AFTER INSERT OR DELETE OR UPDATE ON USERS
  3. FOR EACH ROW
  4. DECLARE
  5. BEGIN
  6. IF inserting or updating or deleting THEN
  7. INSERT INTO AUDIT_TRAIL
  8. (AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
  9. VALUES
  10. (UPPER(v('APP_USER')), SYSDATE, v_trg_action);
  11. END IF;
  12. END AUDIT_TRAIL_USERS_TRIG;

相关问题