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.

[Former Member]

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

[VolkerBarth]

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