This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jan 3, 2013 8:58 PM by BillyVerreynne RSS

Dynamic execution of plsql block inside trigger.

Hema Newbie
Currently Being Moderated
Dear oracle Experts.
Im using the following oracle database.

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

I have one problem in trigger execution. I have a small plsql block in trigger and, I want to execute it as a dynamic way. but it is giving the error. Please find the trigger code. Here my intension is that, the column name used in trigger should be dynamic. In future, if I want to switch the column name, I have to do without modification in trigger.
The error im getting is "ORA-01008: not all variables bound".

Or is there any other way to achieve this. Please suggest your ideas. thank you!!!

CREATE OR REPLACE TRIGGER ETM_AR_IU
AFTER UPDATE ON
EXTERNAL_MAPPING
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW

DECLARE
V_COL_NAME VARCHAR2(30);
PL_SQL_BLOCK VARCHAR2(1000);

BEGIN

SELECT T.COL_NAME INTO V_COL_NAME FROM TRIGGER_TABS_COLS T;
PL_SQL_BLOCK := '
BEGIN
IF :OLD.' || V_COL_NAME || '<> :NEW.' || V_COL_NAME ||
' THEN
INSERT INTO hema_clob VALUES (:NEW.'||V_COL_NAME||' );
END IF;
END;';
EXECUTE IMMEDIATE PL_SQL_BLOCK;
END;
  • 1. Re: Dynamic execution of plsql block inside trigger.
    KeithJamieson Expert
    Currently Being Moderated
    Apart from this being a dumb idea (in my opinion)
    The main problem is that you have embedded quotes.
    You need to either use the q syntax , or you need to double up your quotes as shown here.

    CREATE OR REPLACE TRIGGER ETM_AR_IU
    AFTER UPDATE ON EXTERNAL_MAPPING
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
       DECLARE
           V_COL_NAME VARCHAR2(30);
           PL_SQL_BLOCK VARCHAR2(1000);
        BEGIN
    
            SELECT T.COL_NAME 
            INTO   V_COL_NAME 
            FROM TRIGGER_TABS_COLS T;
    PL_SQL_BLOCK := '
    BEGIN
    IF :OLD.''' || V_COL_NAME || ''' :NEW.''' || V_COL_NAME ||
    ' THEN
        INSERT INTO hema_clob VALUES (:NEW.'''||V_COL_NAME||''' );
    END IF;
    END;';
    EXECUTE IMMEDIATE PL_SQL_BLOCK;
    END;
    Note : Not tested
  • 2. Re: Dynamic execution of plsql block inside trigger.
    BluShadow Guru Moderator
    Currently Being Moderated
    As execute immediate will execute the code as if it's a seperate process from the trigger, that dynamic PL/SQL block is unlikely to have access to the :NEW and :OLD trigger variables.

    Dynamic SQL is bad enough.
    Dynamic PL/SQL is plain stupid.
    Dynamic PL/SQL inside a trigger.... 'nuff said.
  • 3. Re: Dynamic execution of plsql block inside trigger.
    6363 Guru
    Currently Being Moderated
    Hema wrote:

    In future, if I want to switch the column name,
    Why would you want to do that?
    I have to do without modification in trigger.
    That is not possible. If you can rename the column why can you not change the trigger at the same time?
  • 4. Re: Dynamic execution of plsql block inside trigger.
    BluShadow Guru Moderator
    Currently Being Moderated
    3360 wrote:
    Hema wrote:

    In future, if I want to switch the column name,
    Why would you want to do that?
    It's obvious isn't it? Having the ability to change the database design without having to change the applications that use the database is the future of generic power applications!!! ;)
    I have to do without modification in trigger.
    That is not possible. If you can rename the column why can you not change the trigger at the same time?
    Looks to me like a stupid attempt at writing 'generic' code. Any sensible software engineer knows that 'generic' code means 'slow and buggy' code.
  • 5. Re: Dynamic execution of plsql block inside trigger.
    Hema Newbie
    Currently Being Moderated
    My requirement is simple. I have a one master table where the trigger table name and Colums
    Of that table name ll be defined. Now, in trigger, I want to process something based on that trigger column are updated. For example table 'a' having 'x','y' and 'z' column. Now I'm going to write one after update trigger on table 'a' . In trigger, I have to process something if there is a change in column 'x', as of now. In future the same process I want to do, if there s any update on Column 'z' also. That time I can't come and change the code. So I thought of creating one master table, which holds these details and I can fetch the Colum values from that table, and dynamically I can change the column name what ever I want. This s over all picture of my requirement. First please let me know whether is it possible with dynamic plsql within trigger. Thank you all.
  • 6. Re: Dynamic execution of plsql block inside trigger.
    6363 Guru
    Currently Being Moderated
    Hema wrote:

    First please let me know whether is it possible with dynamic plsql within trigger.
    No, it is not possible.

    By the way, what you described is not a requirement, it is an impossible method you have come up with to implementing some, currently unknown, requirement.
  • 7. Re: Dynamic execution of plsql block inside trigger.
    KeithJamieson Expert
    Currently Being Moderated
    If you really want to do this, then you should pass all your old values and new values to a procedure.

    And then let the procedure take care of the action:

    eg
    procedure update_table(p_column1_oldval in table_name.column1name%type,
                                       p_column1_newval in table_name.column1name%type,
                                       p_column2_oldval in table_name.column1name%type,
                                       p_column2_newval in table_name.column2name%type
    )
    is
    begin 
    if p_column1_oldval !=p_column1_newval
    then
      -- do something; eg your insert
      null;  -- placeholder  remove when you have code
    end if;
    if p_column2_oldval !=p_column2_newval
    then
      -- do something like your insert
      null;  -- placeholder  remove when you have code
    end if;
    end update_table;
    and in your trigger, you call the procedure like this
    ...
    for each row
    begin
    update_table(p_column1_oldval  => :old.column1name,
                       p_column1_newval => :new.column1name,
                       p_column2_oldval  => :old.column1name,
                       p_column2_newval=> :new.column1name);
    end;
    At least this way, all the work required is done in the procedure , not directly in the trigger.

    And it doesn't have to be dynamic either. You don't want to do anything unless the new and old values differ for each column.
    You just need 2 parameters for each column in your table in the worst case scenario.


    Personally, I don't like triggers, as they hide the logic. When you are the tracing the code, and you can't seem to find out how a variable is set, its usually because you forgot that there was a trigger on the table.
  • 8. Re: Dynamic execution of plsql block inside trigger.
    Hema Newbie
    Currently Being Moderated
    Hi Keith,
    Thank you for your reply. The solution you proposed is again , I need to pass the values. In future if I want to add another column then I need to modify that procedure. Instead of that I can simply compare the values inside the trigger itself right ? Like, if :old.columname1 <> :new.columnname1 then I can write my logic here. If there is addition of column in future then I need to add one more condition like this in trigger. Then we need a outage and promote to apply in live. To avoid this situation I was thinking to make it as dynamic. As of now, I tried in few ways to implement in the dynamic way, but bind variable is the problem. I think it's is achievable to make it as a dynamic. I need a code change and outage in future, if I want to add one more column in trigger. That's what I understood. I'm trying to think out of box. :( but could not make it.
  • 9. Re: Dynamic execution of plsql block inside trigger.
    6363 Guru
    Currently Being Moderated
    Hema wrote:

    If there is addition of column in future then I need to add one more condition like this in trigger. Then we need a outage and promote to apply in live.
    Why does adding a column not require an outage while also changing the trigger code will require an outage.?
    I think it's is achievable to make it as a dynamic.
    It isn't.
    I need a code change and outage in future, if I want to add one more column in trigger.
    Again, why is modifying the code in a trigger to use the added column different from adding the column in the table itself?
    I'm trying to think out of box
    Try answering the question instead. Why is it not a problem to change the table, but it is a problem to change the trigger on the table?
  • 10. Re: Dynamic execution of plsql block inside trigger.
    rp0428 Guru
    Currently Being Moderated
    >
    Why does adding a column not require an outage while also changing the trigger code will require an outage.?
    >
    The column is being added to the metadata table; it already exists in the actual data table. Adding it to the metadata table would just tell the trigger code to do something with it.

    Bad idea all around as already said since the 'what to do with it' would have to be dynamic also.
  • 11. Re: Dynamic execution of plsql block inside trigger.
    Hema Newbie
    Currently Being Moderated
    Hi,

    Table ABC have columns a, b, and c . For example I wrote my trigger like this only for two column updates ..


    CREATE OR REPLACE TRIGGER ABC_AR_U
    AFTER UPDATE ON
    ABC
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    If :old.a <> :new.a then
    My business logic
    End if;
    If :old.b <> :new.b then
    My business logic again
    End if;
    END;


    In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change. To avoid this, I'm trying to implement in a dynamic way by setting a master table.
  • 12. Re: Dynamic execution of plsql block inside trigger.
    sb92075 Guru
    Currently Being Moderated
    Hema wrote:
    Hi,

    Table ABC have columns a, b, and c . For example I wrote my trigger like this only for two column updates ..


    CREATE OR REPLACE TRIGGER ABC_AR_U
    AFTER UPDATE ON
    ABC
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    BEGIN
    If :old.a <> :new.a then
    My business logic
    End if;
    If :old.b <> :new.b then
    My business logic again
    End if;
    END;


    In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change. To avoid this, I'm trying to implement in a dynamic way by setting a master table.
    How do you "write business logic" for a column that does not yet exist?

    You are trying to solve a problem that does not exist ; except only between your ears.
  • 13. Re: Dynamic execution of plsql block inside trigger.
    6363 Guru
    Currently Being Moderated
    Hema wrote:

    In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change.
    Yes. How many new requirements are you planning on implementing without changing the code? The usual way to implement new features, new requirements and changes to requirements in applications is to change the code or write more code.

    This is normal, it is not a problem. Why do you think it is a problem?
    To avoid this, I'm trying to implement in a dynamic way by setting a master table.
    It is not possible.
  • 14. Re: Dynamic execution of plsql block inside trigger.
    rp0428 Guru
    Currently Being Moderated
    >
    In near future, if my business says that Implement the same business logic , if there is any update happens on third column c , then I need to implement my trigger with one more if statement . For that I need a code change. To avoid this, I'm trying to implement in a dynamic way by setting a master table.
    >
    The flaw in that logic is that the 'what to do' would need to be dynamic also.

    That new column c might need to be handled differently and might even be a different datatype.

    So now you need to dynamically inject the code that says 'what to do' into the trigger from your table.

    You need to abandon this approach and architect a solution based on industry standard practices.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points