Create Trigger with stored procedures by making dynamic in the trigger column

Question

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

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: Encountered the symbol "FOR"

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

Littlefoot · Accepted Answer · 2019-01-31 10:16:47Z

0

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.

Rule of thumb with dynamic SQL:

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;

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.

answered Jan 31, 2019 at 10:16

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robie Yogiansyah Over a year ago

thank you for the suggestion. I just want to do dynamic creation of the column because it is a user requirement. btw what should I do on the loop to retrieve the data column ??

Collectives™ on Stack Overflow

Create Trigger with stored procedures by making dynamic in the trigger column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related