Question Re: ALTER TABLE within trigger in Technology Q&A

ALTER TABLE within trigger

Former Member — Mon, 11 Jun 2012 13:39:47 GMT

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

Re: ALTER TABLE within trigger

Breck_Carter — Mon, 11 Jun 2012 13:51:48 GMT

Not being able to ALTER a table is not just a syntax issue, it is a semantic one: you cannot execute a COMMIT from within a trigger execution, and an ALTER implies a COMMIT. So, the EXECUTE IMMEDIATE does not help, it's still "within the trigger execution".

What you CAN do, if you don't necessarily want the ALTER to be performed right away, is to put the ALTER inside a CREATE EVENT, and then use TRIGGER EVENT to fire the event. An event runs asynchronously, on a separate connection, so it is a "fire and forget" kind of operation that doesn't cause problems for the "calling" connection.

Do not confuse "TRIGGER EVENT" with CREATE TRIGGER, they don't have anything to do with one another.

The CREATE EVENT needs a name, but no schedule or condition, because you are going to use an explicit TRIGGER EVENT.

In your case, you would have to move all the code into the event... as well as not caring that the event might not execute right away.

Re: ALTER TABLE within trigger

VolkerBarth — Mon, 11 Jun 2012 15:51:20 GMT

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

Re: ALTER TABLE within trigger

Breck_Carter — Mon, 11 Jun 2012 16:24:57 GMT

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

Re: ALTER TABLE within trigger

VolkerBarth — Mon, 11 Jun 2012 16:32:32 GMT

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

Re: ALTER TABLE within trigger

Breck_Carter — Mon, 11 Jun 2012 18:03:44 GMT

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.

Re: ALTER TABLE within trigger

Breck_Carter — Mon, 11 Jun 2012 18:09:59 GMT

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

Re: ALTER TABLE within trigger

Former Member — Mon, 11 Jun 2012 19:23:53 GMT

Is this to get around having to separately manage equipment_types?

Re: ALTER TABLE within trigger

VolkerBarth — Tue, 12 Jun 2012 03:43:54 GMT

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

Re: ALTER TABLE within trigger

VolkerBarth — Tue, 12 Jun 2012 06:25:22 GMT

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.

Re: ALTER TABLE within trigger

thomas_duemesnil — Tue, 12 Jun 2012 06:39:47 GMT

Based on Brecks great example here a solution with a Instead of trigger.

INSTEAD OF triggers differ from BEFORE and AFTER triggers because when an INSTEAD OF trigger fires, the triggering action is skipped and the specified action is performed instead.

CREATE TABLE equipment_type (
   type            INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE trained_on (
   id              INTEGER NOT NULL PRIMARY KEY,
   type            INTEGER NOT NULL,
   CONSTRAINT relationship_fixed_by 
      FOREIGN KEY ( type )  
      REFERENCES equipment_type );

CREATE TRIGGER updates_equipment_type
   INSTEAD OF INSERT ON trained_on
   REFERENCING NEW AS new_trained_on
   FOR EACH ROW
BEGIN
    INSERT INTO equipment_type on existing skip VALUES ( new_trained_on.type );
    INSERT INTO trained_on VALUES ( new_trained_on.id, new_trained_on.type );
END;

INSERT trained_on VALUES ( 1, 100 );
COMMIT;
INSERT trained_on VALUES ( 2, 100 );
COMMIT;
INSERT trained_on VALUES ( 3, 200 );
COMMIT;
INSERT trained_on VALUES ( 4, 200 );
COMMIT;

SELECT * FROM equipment_type;
SELECT * FROM trained_on;

type
100
200

id,type
1,100
2,100
3,200
4,200

I have left out the Constraint mandatory_participation_in_fixed_by on the equipment type table is not possible with this solution.

Re: ALTER TABLE within trigger

Breck_Carter — Tue, 12 Jun 2012 10:20:48 GMT

Try this...

Change the trigger to AFTER.

Delete the EXECUTE IMMEDIATE ... ALTER TABLE statements, so only the INSERT equipment_type remains inside the trigger.

Use SET TEMPORARY WAIT_FOR_COMMIT = 'ON' before your INSERT trained_on statements.

Don't code INSERT equipment_type outside the trigger; let the trigger do it.

CREATE TABLE equipment_type (
   type            INTEGER NOT NULL PRIMARY KEY,
   CONSTRAINT mandatory_participation_in_fixed_by 
      CHECK ( type IN ( SELECT type FROM trained_on ) ) );

CREATE TABLE trained_on (
   id              INTEGER NOT NULL PRIMARY KEY,
   type            INTEGER NOT NULL,
   CONSTRAINT relationship_fixed_by 
      FOREIGN KEY ( type )  
      REFERENCES equipment_type );

CREATE TRIGGER updates_equipment_type
   AFTER 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
   INSERT INTO equipment_type  
      VALUES ( new_trained_on.type );
END;

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
INSERT trained_on VALUES ( 1, 100 );
COMMIT;
INSERT trained_on VALUES ( 2, 100 );
COMMIT;
INSERT trained_on VALUES ( 3, 200 );
COMMIT;
INSERT trained_on VALUES ( 4, 200 );
COMMIT;
SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'OFF';

SELECT * FROM equipment_type;
SELECT * FROM trained_on;

type
100
200

id,type
1,100
2,100
3,200
4,200

Re: ALTER TABLE within trigger

Breck_Carter — Tue, 12 Jun 2012 10:24:27 GMT

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

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

Re: ALTER TABLE within trigger

VolkerBarth — Tue, 12 Jun 2012 10:41:26 GMT

"Wow - surely better" than all other replies so far!

And it's even real code:)

BTW: Instead of the "wait_on_commit" option, one might also declare the FK with the CHECK ON COMMIT clause, in case the "automatic type addition" should work always and not only when that particular option is set:

  ...
  FOREIGN KEY ( type )    
  REFERENCES equipment_type
  CHECK ON COMMIT)

Re: ALTER TABLE within trigger

Former Member — Tue, 12 Jun 2012 14:48:43 GMT

Thanks very much for taking the time to write such detailed replies, It has been a great help.

Re: ALTER TABLE within trigger

VolkerBarth — Tue, 12 Jun 2012 14:54:57 GMT

In case any answer is preferrable from your point of, feel free to accept that one - cf. this little HOWTO...

Re: ALTER TABLE within trigger

VolkerBarth — Wed, 13 Jun 2012 07:20:09 GMT

An interesting approach - I surely stumbled on the recursive part (the instead of trigger on trained_on does itself insert into that table), but the docs truly allow that (i.e. prevent a recursive calling) for base tables:

Whether an INSTEAD OF trigger performs recursion depends on whether the target of the trigger is a base table or a view. Recursion occurs for views, but not for base tables. That is, if an INSTEAD OF trigger performs DML operations on the base table on which the trigger is defined, those operations do not cause triggers to fire (including BEFORE or AFTER triggers). If the target is a view, all triggers fire for the operations performed on the view.

Re: ALTER TABLE within trigger

VolkerBarth — Fri, 15 Jun 2012 03:38:25 GMT

And Breck has summed up the whole story of this nice thread in his blog:

Back up, start over, find another way

An "act of penance" worth reading, methinks:)