1 2 Previous Next 17 Replies Latest reply: Jan 3, 2013 10:58 PM by Billy~Verreynne RSS

    Dynamic execution of plsql block inside trigger.

    Hema
      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.
          Keith Jamieson
          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
            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
              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
                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
                  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
                    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.
                      Keith Jamieson
                      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
                        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
                          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
                            >
                            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
                              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
                                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
                                  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
                                    >
                                    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