Rules and commit

[Chris]

Hi
If I have a rule which calls a dbp , triggered AFTER insert.
From the documentation I get that the rule is fired and the dbp called regardless if the transaction rollbacks or commits, because the dbp is considered part of the transaction.
With that I mean that the dbp does not get fired only after the inserted row is committed. Did I get that right?

What happens if inside the dbp I raise a db event which reads the data from the AFTER insert statement, but the transaction
wrapping the dpb or the same dbp rollbacks?
Is the db event called regardless if the transaction rollbacks or commits?, In that case the event can readdirty/ uncommitted or rolled back data?

[Roy Hann]

Chris wrote:

> If I have a rule which calls a dbp , triggered AFTER insert. From the
> documentation I get that the rule is fired and the dbp called regardless
> if the transaction rollbacks or commits, because the dbp is considered
> part of the transaction. With that I mean that the dbp does not get
> fired only after the inserted row is committed. Did I get that right?

That is correct. Some products do support so-called "deferred" rules
that are fired when the transaction is committed but Ingres does not.
Ingres rules execute procedures at the moment the triggering codition
is satisfied, regardless of whether the transaction will later be
rolled back.

> What happens if inside the dbp I raise a db event which reads the data
> from the AFTER insert statement, but the transaction wrapping the dpb
> or the same dbp rollbacks?

Rule fired DBPs execute within the context of the triggering
transaction. The DBP will see the updated data. It doesn't care whether
the transaction is rolled back in the future.

> Is the db event called regardless if the transaction rollbacks or
> commits?, In that case the event can readdirty/ uncommitted or rolled
> back data?

We're venturing into some philosophical territory here. There really
isn't any such thing as a "dirty" read within a transaction because it
is the unit of isolation. And to repeat, the DBP is called as soon as
the triggering condition is satisfied. (A further detail to keep in mind
is that when there are multiple rules that can be triggered at the
same time, the order in which they are triggered is explicitly NOT
defined. If you need things done in a specific order there are ways to
manage it but Ingres doesn't provide any built-in help to do it.)

Roy