Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2004
    Posts
    3

    Unanswered: Trigger Question...

    Hi

    Not sure if this is possible using a trigger so any feedback, suggestions welcome. We are having issues with our DB as the queries etc are gradually degrading due to the huge volumes of data in the tables. Worse still, there is a flag in the table that determines if the record is live and around 85% of the records are generally unused (i.e. not live).

    So what I was hoping to do was create a trigger to fire after the update of a row which would do something similar to the following:

    BEGIN
    IF :NEW.ACTIVE=0 THEN
    INSERT INTO HISTORY VALUES(:NEW.ID, NEWESCRIPT, :NEW.ACTIVE);
    END IF;

    EXCEPTION
    WHEN OTHERS THEN
    -- Consider logging the error and then re-raise
    RAISE;
    END ;

    i.e. When the record is marked unlive (!), a copy is automatically inserted into a HISTORY table. I would also like to extend the trigger to delete the core record after the insertion too.

    However from initial tests, I am getting table mutations errors from Oracle when the trigger fires. I kindof understand why this is happening so am just wondering if there is a work around.

    Maybe call a stored Proc after the UPDATE instead to do this ?

    Any suggestions very welcome.

    Cheers

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Adding records should not increase the query time. I have tables with millions of records and a response time of milliseconds. Are you using indexes? If you are, do you have the correct things indexed. For example, your live and unlive flag. If it was indexex, you could perform queries that would totally skip the unlive records without having to do a full table scan.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Calling a stored proc doesn't help. You could use the usual mutating table work-around for this (see Ask Tom), or you could use DBMS_JOB to schedule another procedure to delete the row after the update is committed.

    Seems odd though: if the effect of setting ACTIVE=0 is actually to delete the record, why not just let the user delete the record?

  4. #4
    Join Date
    Feb 2004
    Posts
    3

    More info..

    Thanks for the quick responses..

    Yep we do use indexes on that (and other) fields and have seen some improvements in some.

    More info on this is that we have inherited a clunky database thats been built ad-hoc and thus isnt as efficient as you would like. Now before you suggest remodelling it, there are thousands of lines of code in our app that would need to revisited. It is a project that will beundertaken, but not in the near future so unfortunately we are in a position of firefighting, and quick fixes where possible

    BEILSTWH -> you're right in what you suggest and looks like i am going to have to add in code to archive the record when it is made inactive, then remove it (we cant just delete it as its needed for audit purposes).

    ANDREWST: -> For the reason mentioned above, the queries are generally very complicated and span several tables. There are many ugly nested selects etc which I know are inefficient. I guess I was hoping for an easy way to implement archiving on the main tables without having to recode some of the app.

    Thanks again....

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Don't delete the record in the trigger, have a DBMS_JOB job that runs every (hour,day,week, whatever) that moves all the records that are marked inactive to the archive tables and then deletes them. This removes the overhead to your application to handle the archiving and off loads it to a standalone job that can take all the time that it needs to do the job.

    ***** Code Fragment *****
    insert into archive_table
    select *
    from active_table
    where inactive_flag = 'Y';

    delete archive_table
    where inactive_flag = 'Y';

    commit;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

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