Finding modified values on update statements without using :old :new
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Finding modified values on update statements without using :old :new

  1. #1
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11

    Finding modified values on update statements without using :old :new

    in an update trigger i want to find these fields.

    thx for u're help
    Share on Google+

  2. #2
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    I need to find the modified columns, without comparing the :new and ld values for all the columns in a table.

    Thx again
    Share on Google+

  3. #3
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    In after/before update triggeres no way without comparing values of fields.
    Share on Google+

  4. #4
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    Yes, I know. I was refering to a database trigger which can be row a row level trigger, and not necesarily a statement level trigger...

    Thx,
    Share on Google+

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Obviously. If you were talking statement level, you wouldn't even have an ld and :new table. And as was stated, there is practically no other way to do it.

    'Practically' means that an impractical solution does exist. That is to create a separate UPDATE trigger for each column (UPDATE of ), but otherwise, ld and :new are your best bet.

    I'm wondering what the issue here is. Are there tons of columns? Why do you need to know which ones changed? Why can't the app tell you this? etc.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong
    Share on Google+

  6. #6
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    The problem is that, we have more than one databases in different locations, but some of them are Oracle Standard edition, which does not permit (as I know, maybe I'm wrong) replication.
    So we will create a synchronizing procedure, which will do the same thing.
    Now if it's an insert or delete statement it's somehow easy, but in case of update we want to pass to the procedure only the modified columns, and the modified values. I've looked for some system views, but without any luck... So if you have ny idea it would be greatly appreciated...

    Thx...
    Share on Google+

  7. #7
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Originally posted by alorincz
    ... some of them are Oracle Standard edition, which does not permit (as I know, maybe I'm wrong) replication.
    The Basic Replication is supported by Standard Edition. You can create read-only snapshots on remote sites and let Oracle to replicate data automatically.
    The Advanced (multi-master) Replication is part of EE only.
    Ales
    The whole difference between a little boy and an adult man is the price of toys
    Share on Google+

  8. #8
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    That's our problem.... We need multi-master replication, so that every database should write his own data on every other database. And that's what we are trying to simulate.
    Share on Google+

  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Well ... great challenge! Good luck!
    Ales
    The whole difference between a little boy and an adult man is the price of toys
    Share on Google+

  10. #10
    Join Date
    Sep 2002
    Location
    Romania, Cluj-Napoca
    Posts
    11
    Thx...

    We've created some audit table, and some linux functions that will do the work, but we need to find the modified columns, so that we won't have to synchronize all the columns in a table.
    I was thinking, that it might be some Oracle system table / view from which we could find the modified columns. I'm looking in the Oracle documentation, but so far without too much luck...
    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width