Results 1 to 11 of 11
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Exclamation Unanswered: Oracle triggers doubt

    Hi,

    If I create a trigger to be executed on insert, update and delete, would the trigger be called for every insert / update / delete happening from within a transaction? In that case, would the trigger be called for the reverse procedure in case the transaction is rolled back?

    eg: I want to log all insert / update / delete operations that happen on a database to a file. If a transaction has 20 inserts, 20 updates and/or 20 deletes happening one by one, would the trigger be called individually for each of them? If it does get called for each individual insert / update / delete, then what would happen if the transaction is rolled back after 10 inserts, 10 updates and/or 10 deletes? Would the file produced by the trigger contain just 10 inserts, 10 updates and/or 10 deletes or would it contain 10 inserts, 10 updates and/or 10 deletes and then the statements for their rolling back too?

    Thanks in advance.
    Dhananjay.

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    it depends on the extra statement in the trigger creation. There are basicly 2 types of triggers, row and statement triggers. The statement triggers fire when a dml statement is executed. So, if i update 20 rows in one statement (update emp set deptno =20), a statement triggers fires only once. U can use this for example for logging whois is doing updates.
    The row triggers fire when ever a row is inserted, updated or deleted. So, is doesn't depend on a transaction, but for every row in the table that is updated, inserted or deleted, the trigger fires. In the row triggers, u can reference thevalues of that particular row. In an insert the new values, in a delete the old values and with update the new and old values.
    I u want the trigger to fire for every row that is inserted, deleted or updated, be sure to use the 'FOE EVERY ROW' statement within the trigger creation statement:

    CREATE [OR REPLACE] TRIGGER XXXX BEFORE/AFTER INSERT on tablename FOR EACH ROW AS [PL/SQL Block]

    Normally it's a good thing to not use all DML actions in one single trigger, but create separate triggers for insert, update and delete statements.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    Hi,

    Thanks for the reply and the helpful explanation.
    I still have one question.
    Let me explain my situation exactly.
    I want to synchronize regional databases with a central database. The regional databases could be using any RDBMS like Oracle, MySQL etc. For doing that, I plan to write a trigger for each database which would produce a file (say, changes.sql) and note down each of the DMLs issued to that database. Then this changes.sql would be sent to the central database where executing all these changes.sql files from different regional databases should give the central database the true picture of the state of the regional databases.

    eg: I want to log all insert / update / delete operations that happen on a database to a file. If a transaction has 20 inserts, 20 updates and/or 20 deletes happening one by one, would the trigger be called individually for each of them? If it does get called for each individual insert / update / delete statement that is executed from an SP inside a transaction, then what would happen if the transaction is rolled back after it was just half way through (say, after 10 inserts, 10 updates and/or 10 deletes)? Would the file produced by the trigger contain just 10 inserts, 10 updates and/or 10 deletes or would it contain 10 inserts, 10 updates and/or 10 deletes and then the statements that the RDBMS executed for rolling them back too?

    I don't want to end up in a situation where only the DMLs executed by SPs are caught by the trigger and logged down even if they are a part of a transaction that had later got rolled back. As a worst case scenario, even if the statements of a failed transaction do get logged, I would like to log the statements to revert back those changes too.
    I hope I've been able to clearly explain what my problem is.

    Regards,
    Dhananjay

  4. #4
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    yes the row triggers fire for eacht row inserted etc. So inserting 20 rows results in 20 trigger executions.
    Logging to a file is not done automaticly. You should write it and control the source to open, write to and close the file. Your problem is indeed the fact that in a transaction multiple inserts can be done, of which the last fails and the whole transaction is rolled back.
    So u have to work around it. I think of the following:

    pre insert/update/delete statement trigger : creating a pl/sq
    Edwin van Hattem
    OCP DBA / System analyst

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    something went wrong. I proceed.

    create a temporary table to store the values of the changed rows.
    in the row triggers u can insert the values into the temporary table.
    in an after statement trigger u open a file, write all records of the temporary table to the file and close it.
    Then, when the transaction commits, all recrods in the temporary table will be deleted.

    Your only instability is that the procedure to write the file, might interrupt the transaction which would have completed succesfully, if it wasn't for the procedure. In that case, u could also write the values to a normal auditing table, and every once in a while u create a file of all record in that table, ordering them in the way the were inserted and deleting them after they have been written to file.

    Hope this helps
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Oct 2003
    Posts
    7
    hi,

    yes!! now this sounds like a much better idea. instead of the trigger writing to the file, i'll let it write to an auditing table. But how will this take care of the transactions? Will all the writes to the temporary table from the trigger be a part of the transaction that's going on in the SP so that if the transaction in the SP fails, all the modifications made in the temporary table would also get rolled back?

    So, that way, only that information, which is committed, would get persisted in the temporary table. In the end, when I have to create the changes.sql file, i just generate the corresponding DML statements from the records of the temporary table.

    The only doubt that i have is - how portable is it? For all RDBMS products which support triggers, does it have the same functionality that any trigger getting fired because of the operations within an SP would automatically become a part of the transaction that the SP has started? If the transaction fails, would the work done in a trigger also get rolled back in all RDBMS products?
    Also, some products don't support triggers at all (I think sybase doesn't or probably some versions of sybase don't). Any suggestions for workarounds in such cases?

    Regards and thanks a lot.
    Dhananjay.

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    that's the beuatiful part of it all. Since u insert from a trigger, transaction control is also done by the original transaction. So if the original update fails after record 19, 19 records already have been inserted in the auditing table. The original transaction fails, rolls back 19 rows in the original tabel and teherefore also rolling back the 19 inserts into the auditing table ! No extra code needed, all original Oracle features ! Isn't is great ?

    If u r going to use auditing tables, and u think portability, just use normal tables instead of temporary tables. older versions of oracle do not support it and not all other rdbms support it.

    For RDBMS that don't support triggers (besides : donn't use it) a solution might be to provide an APOI to the programmers, so that they program there insert into a table goed through the API. In the API (a stored procedure) u can insert the values into the normal table and into the auditing table.

    Hope that helps.
    Edwin van Hattem
    OCP DBA / System analyst

  8. #8
    Join Date
    Oct 2003
    Posts
    7
    Thanks a ton Evan,

    You've been a GREAT help... :-))
    Yes!! this is a really cool feature. Unfortunately not supported by some other vendors.
    The API is a great idea for an application that is in the early stages of development or for code that you have control over but in this case, I'm having to work with existing applications with 0 control over the source code. So, I don't have that API luxury.
    About the solution for database synchronization between databases on different RDBMS products, I'm also having to contend with the fact that this solution has a limitation of being able to work only if the RDBMS product supports triggers. But at least now, I have something to start working on (despite the limitation).
    Can you suggest some other cool portable approach without using triggers?

    Thanks a lot again.
    Dhananjay

  9. #9
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    if u really have no other option,, u might consider to build a comparison utility, but that would be a lot of work.In that case u take a sort of a snapshot, since u cannot determine the moment of DML statements.
    So in that case u create a compare table, similar to the productioin table and load the data of the production table into it. Then at a certain moment, u select all records in the production table which are somehow different from the records in the compare table. And change the data in the compare table as well for the next time u compare those two.
    This is more work, and will not be as accurate as working with triggers, but at least u have something.

    Hope that helps.
    Edwin van Hattem
    OCP DBA / System analyst

  10. #10
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by evanhattem
    hi,

    if u really have no other option,, u might consider to build a comparison utility, but that would be a lot of work.In that case u take a sort of a snapshot, since u cannot determine the moment of DML statements.
    So in that case u create a compare table, similar to the productioin table and load the data of the production table into it. Then at a certain moment, u select all records in the production table which are somehow different from the records in the compare table. And change the data in the compare table as well for the next time u compare those two.
    This is more work, and will not be as accurate as working with triggers, but at least u have something.

    Hope that helps.
    Hmmm....
    That is a lot of work..... not just for me but also for the RDBMS. If the table has a thousands and thousands of records and if the database has hundreds and hundreds of tables with thousands and thousands of records in almost every table, then this solution won't be the best fit, right?
    So, I'll just let this trigger approach stay for this 'version' of my 'solution' and I'll look for something else and more portable for the ever elusive 'next' version. :-))
    I don't see any other solution right now.

    Thanks a lot.
    Regards,
    Dhananjay

  11. #11
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You should take a look at Oracle Replication. It is all about synchronizing databases.

    http://download-west.oracle.com/docs...a96567/toc.htm

Posting Permissions

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