12 Replies Latest reply: Jul 26, 2013 11:59 AM by John Spencer RSS

    update trigger on update in same table

    whitecat

      Hi guys!

       

      I have a newbie question as am a newbie in this domain:

       

      There is one table : table1 which looks like:

      Col1

      Col2

      row1

           1

      row2

           2

      row3

           3

       

      I need to write a simple trigger to achieve:


      * whenever there is change in row2 for  col2 value ('2') to some other value (e.g. '5') , i need to reset the value back to its original value('2').


      I tried writing simple trigger for this but since both operations(original update statement and my trigger update statement) are on same table it throws some error like... cant handle more than 50 recursions or invalid trigger etc.


      * also pleas let me know, if there is any other better way around rather than using trigger. Is there any way to monitor at schema level etc. that can track that there has been some data update and then immediately run my update trigger on that...


      * 1 idea I can think of is : create another table with same data update it as needed and copy back to original table, but it looks more of work + authority problem too...

        • 1. Re: update trigger on update in same table
          Billy~Verreynne

          What is the business reason/data integrity reason behind this?

           

          And why does the rule only apply to row 2? How is row 2 (or rows like it) identified?

          • 2. Re: update trigger on update in same table
            Raunaq

            You can use the following in your trigger

             

            :new.col2:=:old.col2

            • 3. Re: update trigger on update in same table
              whitecat

              yes the data integrity is the reason.

               

              for one specific row it has to be fixed value, however update modifies all rows

               

              * row can be identified as :

               

              .... where col1 = 'row2'....

              • 4. Re: update trigger on update in same table
                whitecat

                so, will it solve the mutating error which i am getting on for same table trigger..please suggest, i am not expert..

                • 5. Re: update trigger on update in same table
                  Billy~Verreynne

                  24e0732f-fb99-46c1-85a3-6303aadf695a wrote:

                   

                  yes the data integrity is the reason.

                  That still does not explain WHAT the data integrity rule is. One needs to understand the WHAT and WHY, before coming up with the HOW.

                   

                   

                  for one specific row it has to be fixed value, however update modifies all rows

                   

                  * row can be identified as :

                   

                  .... where col1 = 'row2'....

                  Not good enough. As that means the trigger needs to have the text 'row2' hardcoded as a check. And what happens if row200 needs to be treated the same fashion when the rule in the trigger only works for row2?

                   

                  And why only for row2? If row2 is so special and unique, and treated differently from each and every other row in that table, wtf is it doing in that table? Surely that row (despite similar attributes) is a different type of entity than what the other rows in that table represents - which means it should be in a different table? Or the existing entity's design is flawed.

                   

                  As I said - we need to understand the problem.All you have offered is a broken solution (mutating trigger/hardcoded column values in trigger) - how do you expect this forum to fix that broken solution if the problem that the solution need to address is unknown?

                  • 6. Re: update trigger on update in same table
                    Manik

                    Check this link.. My suggestion would be , dont show this record to the users at all for update.

                    http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm

                     

                    Cheers,

                    Manik.

                    • 7. Re: update trigger on update in same table
                      whitecat

                      thanks Billy for your answer and suggestions for thinking the solution on large scale.

                      But, somehow you might understand that when we work in huge enterprise system, you may not have all the privileges you need to implement the perfect solution as we can get in school project where the whole system is under one man control. Moreover, it might go under long chain of approvals, review etc. from geographically spread team which might only delay the quick fix.

                       

                      Well, to your query on what, why how. I can say in short :

                      * that table is getting updated by a third party vendor system(not in our control) so we can't touch that code(<--probably the best fix)

                      * wtf this row doing in this table : this is very old system designed with some specific purpose, down the line biz requirements changed for regulatory compliance etc. and only that row needs to keep a FIXED value, while other rows keep on getting modified as usual from external system.

                      * you are right, it should have been in a different table, but do you think it as quick fix for the reasons I mentioned above.

                       

                       

                      ** So, what I am thinking is : just write a trigger --> to get its old value back after every update on this table.

                       

                      well I appreciate you answer though

                      • 8. Re: update trigger on update in same table
                        1024977

                        You can overcome the Mutating table error by using Pragma Autonomous_transaction.

                        But after that deadlock can occur...Please try.

                        • 9. Re: update trigger on update in same table
                          whitecat

                          Pragma not working.. please help

                          I tried creating VIEW on the same table and creating INSTEAD OF trigger on this view... it works fine this way..

                          But the problem is : this trigger gets fired on UPDATE on VIEW.... but it doesnt get fired on UPDATE on this table..

                          • 10. Re: update trigger on update in same table
                            rp0428

                            Raunaq already told you how to do it.

                            • 11. Re: update trigger on update in same table
                              whitecat

                              i wrote :

                               

                              CREATE OR REPLACE TRIGGER table1_t1

                              after UPDATE

                              ON table1

                              referencing old as old new as new

                              for each row

                              when (old.col1 = 'row2')

                              BEGIN

                              new.col2 := 2

                              END;

                              /

                               

                              it still doesnt work.. throws error ora-04098:invalid / failed etc...

                              • 12. Re: update trigger on update in same table
                                John Spencer

                                A couple of problems with that.  You need to do it as a before insert trigger in order to change the value.  You also need a colon (:) before the new keyword and a semi-colon (;) at the end of the assigment statement.

                                 

                                John