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.

[thomas_duemesnil]

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.

[VolkerBarth]

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.