通过在触发器列中创建动态来使用存储过程创建触发器 [英] Create Trigger with stored procedures by making dynamic in the trigger column

问题描述

我使用存储过程创建新的触发器审核，原因是触发器审核中需要灵活的列

Im create new trigger audit using store procedure cause want flexible column in the trigger audit

im使用Oracle 12 C ..

im using Oracle 12 C ..

CREATE OR REPLACE PROCEDURE DBADMIN.TEST3 (OUTPUT          OUT SYS_REFCURSOR,
                                          TABLE_NAME   IN     VARCHAR2)
IS
    N   NUMBER;
BEGIN
    N := 0;

    EXECUTE IMMEDIATE '

CREATE OR REPLACE TRIGGER DBADMIN.TA_EMPLOYEES3
    AFTER INSERT OR DELETE OR UPDATE
    ON DBADMIN.EMPLOYEES
    FOR EACH ROW
DECLARE
    SID   VARCHAR2 (30);
BEGIN
    SELECT SYS_CONTEXT ('' USERENV       '', ''    IP_ADDRESS          '') INTO IP FROM DUAL;

    SELECT SEQ#
      INTO SID1
      FROM v$session
     WHERE audsid = (SELECT USERENV ('' SESSIONID '') FROM DUAL);

    IF INSERTING
    THEN
        INSERT INTO DBADMIN.DBLOG_MONITORING_DETAIL2 (SID,
                                                          COLUMNS,
                                                          OLDVALUE,
                                                          NEWVALUE)
                 VALUES (SID1,
                         i.COLUMN_NAME,
                        'for row in (SELECT column_name from user_tab_columns where table_name=''EMPLOYEES'' loop  
                        execute immediate '':old.row.column_name '';
                      end loop;
/

32 26 PLS-00103:出现符号"FOR"

32 26 PLS-00103: Encountered the symbol "FOR"

我认为脚本中的逻辑不好..可以给我更好的逻辑或更好地修复我的脚本?

i think im bad logic in my script .. can give me better logic or repair my script its better ?? .

推荐答案

在Oracle中，您实际上应该很少使用动态SQL创建数据库对象.人们有时会滥用该功能，在我看来，这就是您要尝试执行的功能.您可以做到这一点并不意味着您应该做到这一点，例如您可以用铅笔戳一下眼睛，但是不应该这样做.

In Oracle, you should really rarely use dynamic SQL to create database objects. People sometimes abuse that functionality which is - in my opinion - what you're trying to do. The fact that you can do it doesn't mean that you should do it, e.g. you can poke your eye with a pencil, but you shouldn't do that.

使用动态SQL的经验法则:

Rule of thumb with dynamic SQL:

• 忘记EXECUTE IMMEDIATE
• 创建局部变量(较大的VARCHAR2或CLOB，具体取决于您的工作)
• 编写语句(在您的情况下为CREATE TRIGGER)和
• 将其存储到该变量中
• 使用DBMS_OUTPUT.PUT_LINE
将其显示在屏幕上
• 将其复制/粘贴并作为独立语句运行
  • 如果成功，则说明您做得很好，因此您现在可以在EXECUTE IMMEDIATE
  中使用它
  • 如果失败，您将不得不加倍努力，对其进行调试，修复错误并重复该循环
  • forget about EXECUTE IMMEDIATE
  • Create a local variable (a large VARCHAR2 or CLOB, depending on what you're doing)
  • compose the statement (CREATE TRIGGER in your case) and
  • store it into that variable
  • display it on the screen using DBMS_OUTPUT.PUT_LINE
  • copy/paste it and run it as a standalone statement
    • if it succeeds, you've done a good job so you can now use it in EXECUTE IMMEDIATE
    • if it fails, you'll have to try a little bit harder, debug it, fix errors and repeat the cycle

    从您得到的错误信息开始:​​

    As of the error you got: this:

    for row in (SELECT column_name from user_tab_columns where table_name=''EMPLOYEES'' loop  
      execute immediate '':old.row.column_name '';
    end loop;
    

    作为INSERT INTO目标无效；您不能以这种方式混合使用SQL和PL/SQL.无论您使用的是动态SQL还是完全不起作用.此外，很明显这是错误的(缺少右括号，您立即执行到底是什么?old_row.column_name?您如何执行一个列名?

    is invalid as the INSERT INTO target; you can't mix SQL and PL/SQL that way. It won't work at all, regardless dynamic SQL you use. Besides, it is obvious that it is wrong (missing closing bracket, what exactly are you executing immediately? old_row.column_name? How would you execute a column name?

    在我看来(再次)，您不应该那样做.如果要创建触发器，请执行此操作-但不要动态地.

    In my opinion (once again), you shouldn't do it that way. If you want to create a trigger, do it - but not dynamically.

    这篇关于通过在触发器列中创建动态来使用存储过程创建触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！