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.

[Breck_Carter]

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.