Triggers and transactions...any way to defer trigger execution?

Discussion in 'microsoft.public.sqlserver.programming' started by Jonathan Furman, Oct 11, 2004.

  1. Hello everyone. I was hoping that someone could give me a quick answer to a
    trigger question. The question is: do AFTER UPDATE and AFTER DELETE triggers
    always fire exactly when the UPDATE or DELETE statement is issued,
    regardless of any pending explicitly defined transactions?

    The reason I ask is that I would like to use a trigger to do some data
    verification, but it would make things much much easier if the trigger
    didn't fire until a COMMIT was issued at the end of a batch of DML commands
    and the final resultant state was tested at that point rather than at each
    statement within the batch.
    If there is no way to defer the trigger than I'll have to do a lot of
    juggling of data to keep the everything consistent at each point of the
    batch. Hope my question makes sense.

    Thank you in advance to any takers on this question.

    Jonathan Furman, Oct 11, 2004
    1. Advertisements

  2. Why don't you make the update itself conditional on the data? Then you
    don't have to worry about the trigger. If you are letting people edit the
    tables directly, just stop doing that and limit manipulation to your stored
    procedures (then you can possibly eliminate triggers altogether).
    Aaron [SQL Server MVP], Oct 11, 2004
    1. Advertisements

  3. Hi Jonathan,

    This is not possible.

    I've heard that the ANSI SQL standard does include the concept of deferred
    constraint checking. This is also not (yet?) available in SQL Server. I
    can imagine that, shoud MS one day decide to introduce deferred constraint
    checking, deferred trigger execution might be offered as well. But maybe
    this is more wishfull thinking than actual foresight.

    For now, you are stuck with either the juggling of data you mention in
    your post, or the update-through-stored-proc-only approach Aaron suggests.

    Best, Hugo
    Hugo Kornelis, Oct 11, 2004
  4. Thank you for both of your replies. That pretty much clears it up for me. I
    can see the need for a "deferred trigger/constraint" so I'm not surprised
    that there is such a thing defined in ANSI SQL. It sure would simplify
    procedure coding to not have to juggle data as the system transitions from
    state A to state B just to stay consistant when only the initial and final
    states are what's really important. Maybe in the future...then. OK I will
    juggle..I still refuse to use cursors to do it though!

    Thanks again.

    Jonathan Furman, Oct 11, 2004
  5. May I make a suggestion. I have faced this problem before, and the
    following solution has occurred to me, although I have never
    implemented it:

    Suppose I have the following DB:

    CREATE TABLE Widget (WidgetID int primary key, WidgetAttachment int,
    Weight float)
    CREATE TABLE Attachment (AttachmentID int primary key, Weight float)

    And I want to ensure, at the end of each transaction, that the Weight
    of the attachment is less than the weight of the widget; but allow
    inconsistencies within a transaction. I could do something like this:

    CREATE TABLE #UpdatedWidgets (WidgetID int)
    CREATE TABLE #UpdatedAttachments (AttachmentID int)

    I could then put triggers on the Widget and Attachment tables which
    would insert rows into #UpdatedWidgets, #UpdatedAttachments, with the
    ID of the attachment/widget affected by the DML operation...

    Finally, instead of calling COMMIT directly, I create a stored
    procedure sp_COMMIT, which works as follows:

    For every entry in #UpdatedWidgets, #UpdatedAttachments check that the
    relevant entries in the Widget and Attachment tables are consistent.

    DELETE FROM #UpdatedWidgets
    DELETE FROM #UpdatedAttachments

    If yes,
    RETURN 0
    RAISERROR ('Consistency violation....',18,-1)
    RETURN 1

    As I said, I've never tried this, but it should work, so long as your
    code always calls sp_COMMIT rather than COMMIT directly...

    But I think it won't work if you are using distributed transactions
    though (which is why I didn't use it in my case, because I was using
    distributed transactions...)

    Simon Kissane
    Macquarie University
    Simon Kissane, Oct 12, 2004
  6. Wow...I like that. Kind of a "shadow constraint" [updated] or [inserted]
    table. Very very clever. I appreciate you sharing that technique. I would
    still categorize your technique as juggling, but it is smooth!

    What I've decided to do is to right now is to just remove the trigger and
    leave it up to my stored procedures to do the right thing. The project I'm
    working on is a prototype, once I get it all functionally working I'll
    probably revisit integrity when I build indexes and so forth. As the
    users/client applications have no direct access to the tables I should be
    safe for at least the time being.

    Simon, again, thanks for the tip and I hope things are well "down there"!

    Jonathan Furman, Oct 12, 2004
    1. Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.