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.


Jon

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).

--
http://www.aspfaq.com/
(Reverse address to reply.)




[quoted text, click to view]

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.

Jon

[quoted text, click to view]

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
--

[quoted text, click to view]

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,
COMMIT
RETURN 0
Else,
RAISERROR ('Consistency violation....',18,-1)
ROLLBACK
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...)

Cheers
Simon Kissane

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"!


Jon