ALTER TABLE within trigger

[Former Member]

I am stuggling to create a trigger that drops a constraint using ALTER TABLE I have read that this command cannot be used within a trigger so have included it as a string. All the code has been tested outside of a trigger and works correctly, any help would be appreciated.

The error I am currently getting is 'commit rollback not alowed within atomic operation'

CREATE TRIGGER updates_equipment_type
BEFORE INSERT ON trained_on
REFERENCING     NEW AS new_trained_on
FOR EACH ROW
WHEN (new_trained_on.type NOT IN (SELECT type FROM equipment_type))
    BEGIN
    EXECUTE IMMEDIATE       'ALTER TABLE trained_on
                            DROP CONSTRAINT relationship_fixed_by
                            ALTER TABLE equipment_type
                            DROP CONSTRAINT mandatory_participation_in_fixed_by';
                            INSERT INTO equipment_type
                            VALUES (new_trained_on.type);
    EXECUTE IMMEDIATE       'ALTER  TABLE trained_on
                            ADD CONSTRAINT relationship_fixed_by
                            FOREIGN KEY (type)
                            REFERENCES equipment_type
                            ALTER TABLE equipment_type
                            ADD CONSTRAINT mandatory_participation_in_fixed_by
                            CHECK (type IN (    SELECT type
                            FROM trained_on))';

    END

[Former Member]

Is this to get around having to separately manage equipment_types?

[VolkerBarth]

Taking the check into the trigger code is surely more flexible (though usually less comprehensible, too, but that does not seem to matter here).

However, I had the impression the insert should not fail in these cases but the missing parent record should be "fixed" - whereas a RAISERROR (or ROLLBACK TRIGGER, which I would prefer) would completely undo the insert, as well - at least unless the trigger contains according error-hanlding code.

[VolkerBarth]

While I fully follow Breck's advice in general, in the current case, this won't work as expected IMHO:

You seem to want to disable a CHECK constraint and a FOREIGN KEY constraint for the current table while entering "violating" data, so the desired solution to

• drop the constraints,
• insert the missing data in the parent table and
• re-add the constraints

requires correct timing, which won't be guaranteed with the asynchronous EVENT solution.

There are possibly different approaches if you need to disable any logic during particular DML statements on a regular basis - which would not require to ALTER TABLE in between (which will not scale very well, anyway, in case that might matter...).

E.g. you might use a particular configuration table that can be used to disable checks for particular users or connections. Cf. this FAQ for a similar question on disabling trigger actions...

We could surely give some sample code if you can elaborate what exactly you're trying to achieve. Ideas would include delayed foreign key checks with the "wait_for_commit" option (to add the missing parent entry "on demand") or a check including a CREATE VARIABLE to disable the check temporarily...

[Breck_Carter]

"requires correct timing" - that's why it says "you would have to move all the code into the event" 🙂

[VolkerBarth]

OK, but would that not conflict with the INSERT statement itself?

Re-reading the question, I would think that

• using wait_for_commit and simply inserting the missing equipment_type entry during the insert into trained_on
• or even checking for missing parent entries before doing the insert

would be much simpler - but that's just my understanding of the question...

[Breck_Carter]

First of all, wait_for_commit doesn't affect the CHECK constraint that is dropped and added... if the CHECK is going to fail, it will still fail.

Second of all, dropping and recreating a foreign key around a single INSERT seems pointless... if the INSERT causes a violation, the ALTER TABLE ADD foreign key will raise it. Generally, dropping and recreating foreign keys, or wait_for_commit, is ONLY useful if you have two or more operations... one which violates, and another which repairs the violation. That is not the case here, which implies the dropping and recreating the foreign key is pointless.

I suspect more work needs to be done here... BUT, please tell me how moving all the code into an event would cause a conflict (or at least, a conflict that isn't already there 🙂

...by "all code" I mean the four ALTERs and one INSERT.

[Breck_Carter]

BTW, I am in agreement with your suggestion to "find another way".

[VolkerBarth]

Yes, "wait_for_commit" would not resolve the CHECK constraint issue - therefore I've hinted at a "check including a variable to disable the check temporarily...".

The conflict might arise between the "INSERT trained_on ..." and the event code when triggered :

Obviously we're dealing with an "INSERT trained_on ... " that tries to insert an equipment_type which does not already exist in the latter table.

In that case the according FK constraint will fail, unless the FK is already dropped from within the event before the INSERT statement is finished - or, in case "wait_for_commit" is set, before it is committed. That's the dangling timing issue I see. - In other words: I would think the event code must be run before the INSERT is finished, and that would not be guaranteed simply because of the asynchronous nature of event connections.

---

Correction: IMHO the ALTER TABLE cannot succeed timely, as the following situation clearly would be satisfied:

"ALTER TABLE is prevented whenever the statement affects a table that is currently being used by another connection".

[Breck_Carter]

Ah, yes, well... I offer a money-back guarantee on all my free advice!

...perhaps my second reply will serve as penance?