On Apr 8, 2021, at 11:17 AM, Roy Hann <specially@processed.almost.meat> wrote:

I just stubbed my toe on a restriction I am amazed I didn't know about.

I have a REFERENCES constraint referring to a table that is effectively
a homebrewed function index. I wanted to define a BEFORE rule on the
"base" table that would compute and insert the required row in the
"index" before the row is inserted in the base table. (It's not really
an index; I planned to use the PK value rather than a TID.)

I am dismayed to be told "E_US18B5 CREATE RULE: Data change statements
are not permitted in procedures triggered by BEFORE rules".

My recollection is that the Standard doesn't allow data change statements in BEFORE triggers either, although I'd have to double check to be 100% positive.

But I am puzzled why it makes sense to preemptively limit BEFORE rules
in this way.

It's an implementation constraint, mostly. There's a data change statement
in progress, with DMF positioned and data in DSH rows and what-not,
and we simply don't expect to hare off in the middle of that and execute a totally different statement. AFTER rules are different because at that point, the firing statement is essentially complete.

I rather suspect that it could be made to work, perhaps with some specific restrictions; especially after some recent unreleased cleanup work in QEF.
It wouldn't be trivial to validate all of the possible code paths and fix whatever
would need to be fixed.

Karl

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Karl Schendel wrote:

I am dismayed to be told "E_US18B5 CREATE RULE: Data change statements
are not permitted in procedures triggered by BEFORE rules".

My recollection is that the Standard doesn't allow data change statements in BEFORE triggers either, although I'd have to double check to be 100% positive.

If it is in fact standard behaviour my cynical guess would be that
everyone reasoned as you do below and just said "...nah." :-)

But I am puzzled why it makes sense to preemptively limit BEFORE rules
in this way.

It's an implementation constraint, mostly. There's a data change statement in progress, with DMF positioned and data in DSH rows and what-not,
and we simply don't expect to hare off in the middle of that and execute a totally different statement. AFTER rules are different because at that point,
the firing statement is essentially complete.

I rather suspect that it could be made to work, perhaps with some specific restrictions; especially after some recent unreleased cleanup work in QEF.
It wouldn't be trivial to validate all of the possible code paths and fix whatever
would need to be fixed.

Well it turns out I was being too clever and too stupid at the same time
(as usual). A user-defined AFTER rule fires before a system-defined rule--probably for this very reason, so I am able to update the
function index with an AFTER rule before the FK constraint is checked.

So all is well.

Roy

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)