Rollback in ... How do you get Triggers and Constraints to "get along"?

Jurij Modic wrote:

[...]

> If you don't use explicit COMMIT/ROLLBACK in your
> trigger, the whole transaction will be rolled back if integrity
> constraint is violated. It is irrelevant if actions before rollback are
> performed by trigger, procedure, aplication, manually ....

OK ... this brings me to a question.

We have some triggers we inherited from others, and many of them are AFTER TRIGGERS that include ROLLBACK and COMMIT statements. Many of them appear to work.

BUT ... we have some OTHERS that give "ERROR 4092, Cannot ROLLBACK in a trigger." The manual on this error says that neither commit nor rollback may be used in a trigger.

BUT ... why don't the other ones bomb? (Hmmm ... they may be disabled, I'll have to double-check.)

Now, this isn't a serious problem. The way these triggers were written, they can easily (and more properly) be written as BEFORE triggers (they're rolling back a transaction that fails an integrity check, or committing if it succeeds, the way we did in Sybase, which =only= has after-triggers.)

But I'm curious: you are referring to rollback/commit in triggers as if it's OK. I'm wondering if it =is= OK under the right conditions.

If it makes a difference, we're using Oracle 7.3. Perhaps older revisions weren't so picky.

A. Received on Tue Jan 14 1997 - 00:00:00 CST