Forum Stats

  • 3,817,244 Users
  • 2,259,294 Discussions
  • 7,893,711 Comments

Discussions

detect DML failure and prevent trigger logic from executing

User_IKSEG
User_IKSEG Member Posts: 4 Red Ribbon

we have an AFTER EACH ROW trigger that fires for INSERT/UPDATE/DELETE - from what I understand integrity constraints are not checked until after all triggers are fired and executed - our issue is that we need to prevent the AFTER EACH ROW trigger logic from executing when the DML will result in a failure (for any reason)

in the example we are working with we have a DELETE that results in an ORA-02292: integrity constraint (FSDBA.ORG_CONT_FK) violated - child record found - but I can't seem to detect this violation before we execute the trigger logic - this is just an example

any suggestions would be greatly appreciated

Allison

Tagged:

Answers

  • Paulzip
    Paulzip Member Posts: 8,678 Blue Diamond

    No, that is not necessarily true. Integrity constraints can be checked on issuing a before row trigger, it depends.

    Please read "The Execution Model for Triggers and Integrity Constraint Checking", although it's in an older document, the rules still apply,

    Why do you need to prevent your trigger from firing due to ref integrity? Is your trigger performing DML? If so, Triggers are part of the transactional control, so anything they do will be rolled back anyway - unless you're doing something stupid like issuing a commit in a trigger or trying to do DDL, which will issue an implicit commit.

    The question is, WHY are you trying to do this?

  • User_IKSEG
    User_IKSEG Member Posts: 4 Red Ribbon

    the after trigger issues an insert/update/delete (delete in this case) and if there is an issue we throw an exception - the message to the user is for the exception in the after each row trigger and not the actual source DML constraint issue - we want to prevent the execution of the after each row trigger if the original source DML has a problem with integrity checking - of course if we don't raise an exception message in the after each row trigger then the message to the user reflects the originating DML error if one exists - basically we want to handle either error (if one occurs) depending on where the error actually exists - originating DML exception if failing or the after each row DML if the originating DML passes and the after each row DML fails - I'm open to suggestions otherwise - thank you in advance of course :)

  • User_IKSEG
    User_IKSEG Member Posts: 4 Red Ribbon

    one other note - we have a before each row trigger for delete already in place and still the integrity constraint violation is not thrown until after all trigger processing is complete - and ultimately the after each row dml is executed no matter - we want to prevent the subsequent dml in the after each row trigger from executing

  • Paulzip
    Paulzip Member Posts: 8,678 Blue Diamond
    edited Apr 4, 2021 6:48PM

    I may be wrong, but I suspect you can't achieve what you want, preventing the execution of the trigger. The FK constraints won't be validated in time.

    Potential options you might try:

    • Take a look at compound triggers. They give a much more flexible scope of control on a sequence of DML on a table in one place, you get BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT in one trigger. You may be able to setup what you would be doing in your AFTER EACH ROW to be run in your AFTER STATEMENT, which may give you the sequential control you need. You'd have to try it out as I can't say as you haven't provided any source code.
    • Check the parent / child relationships pre-emptively and prevent execution
    • Trap the relevant exception in the application code