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.

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

  ...
  FOREIGN KEY ( type )  

  REFERENCES equipment_type
  CHECK ON COMMIT)

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

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.