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]

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

[VolkerBarth]

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

[VolkerBarth]

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:)