Back up, start over, find another way

Sometimes you can solve a problem by backing up, starting over and taking a fresh look at the requirements.

How far should you back up? At least to the point you starting having trouble.

Here's a case in point:

Question: How do I get this trigger to work? I get SQLCODE -267 COMMIT/ROLLBACK not allowed within atomic operation.

CREATE TRIGGER trigger_before_insert_t2
   BEFORE INSERT ON t2
   REFERENCING NEW AS new_t2
   FOR EACH ROW
   WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
BEGIN

   ALTER TABLE t2 
      DROP CONSTRAINT t2_is_a_child_of_t1;

   ALTER TABLE t1  
      DROP CONSTRAINT check_that_t1_type_exists_in_t2;

   INSERT t1 VALUES ( new_t2.type );

   ALTER TABLE t2  
      ADD CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1;

   ALTER TABLE t1  
      ADD CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) );

END;

INSERT t2 VALUES ( 1, 100 );

COMMIT/ROLLBACK not allowed within atomic operation
SQLCODE=-267, ODBC 3 State="42000"

First Wrong Answer...

ALTER TABLE implies COMMIT, so try wrapping the ALTER TABLE statements with EXECUTE IMMEDIATE statements.

EXECUTE IMMEDIATE is wonderful for solving "you can't do that here" problems, like coding a FORWARD TO statement inside a stored procedure.

Yabbut...

SQLCODE -267 is not just a syntax issue, it is a semantic one:

• The "INSERT t2" statement is an atomic operation,
  
• and therefore, since the INSERT fires the trigger,
  
• the whole trigger execution is part of the same atomic operation,
  
• and since EXECUTE IMMEDIATE doesn't move the ALTER TABLE statements outside the trigger execution, say, to another thread or connection,
  
• the ALTER TABLE statements and their implied commits are still inside the trigger execution
  
• and therefore the ALTER TABLE statements and their implied commits are still within the atomic operation
  
• which is not allowed.
  

Second Wrong Answer...

ALTER TABLE implies COMMIT, so try moving the code to a CREATE EVENT.

CREATE EVENT is wonderful for solving problems involving commits, like implementing nested transactions. When you use TRIGGER EVENT to execute an event, and that event does a commit, it doesn't affect the caller's transaction (where "caller" is the code that issued the TRIGGER EVENT)... the reason is, TRIGGER EVENT starts a new connection to run the event, and a transaction (inserts, updates, ..., commit) run on that connection is separate from the transaction running on the caller's connection.

TRIGGER EVENT is like a whole new user, one who works out of sight, doesn't need a keyboard or a monitor, and never asks for a raise.

Yabbut...

Events run asynchronously,

• which means TRIGGER EVENT is a "fire and forget" operation with no guaranteed starting time and no guaranteed end time,
  
• and not only that, it never returns to the caller
  
• so the caller can't tell when it's safe to proceed.
  

So what's the big deal? Here's the big deal: The CREATE TRIGGER was coded with the BEFORE clause to guarantee the inner "INSERT t1" runs before the outer "INSERT t2".

If the "INSERT t1" is moved into a CREATE EVENT, it might (and probably will) execute after the outer "INSERT t2".

Are you sure? Let's try it!

In the following, all the code from the CREATE TRIGGER has been moved into a CREATE EVENT, and

• the trigger uses the TRIGGER EVENT statement on lines 34 and 35 start the event,
  
• the funky argument list on line 35 is used to pass the new value of t2.type to the event
  
• where the EVENT_PARAMETER logic on lines 9 and 10 converts the funky argument back into an integer.
  

The code on lines 12 to 19 isn't quite "all the code" from the trigger; these two ALTER statements have been left out altogether because the SET TEMPORARY OPTION WAIT_FOR_COMMIT statement on line 42 is a much easier, safer and more efficient way to temporarily turn off foreign-key-checking:

ALTER TABLE t2 
      DROP CONSTRAINT t2_is_a_child_of_t1;

   ALTER TABLE t2  
      ADD CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1;

CREATE EVENT event_on_insert_t2
HANDLER BEGIN

   DECLARE @string_type VARCHAR ( 128 );
   DECLARE @type        INTEGER;

   MESSAGE 'EVENT started' TO CONSOLE;

   SET @string_type = COALESCE ( EVENT_PARAMETER ( '@passed_type' ), '0' );
   SET @type        = CAST ( @string_type AS INTEGER );

   ALTER TABLE t1  
      DROP CONSTRAINT check_that_t1_type_exists_in_t2;

   INSERT t1 VALUES ( @type );

   ALTER TABLE t1  
      ADD CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) );

   MESSAGE 'EVENT finished' TO CONSOLE;

END;

CREATE TRIGGER trigger_before_insert_t2
   BEFORE INSERT ON t2
   REFERENCING NEW AS new_t2
   FOR EACH ROW
   WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
BEGIN

   MESSAGE 'TRIGGER started' TO CONSOLE;

   TRIGGER EVENT event_on_insert_t2 
      ( @passed_type = STRING ( new_t2.type ) );

   MESSAGE 'TRIGGER finished' TO CONSOLE;

END;

MESSAGE 'Test started' TO CONSOLE;
SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
MESSAGE 'INSERT started' TO CONSOLE;
INSERT t2 VALUES ( 1, 100 );
MESSAGE 'INSERT finished' TO CONSOLE;
COMMIT;

No joy!

The WAIT_FOR_COMMIT did work as advertised (it delayed the referential integrity error until the COMMIT)

...but the error still happened:

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"
Line 6, column 1
COMMIT

Test started
INSERT started
TRIGGER started
TRIGGER finished
EVENT started
INSERT finished

Why?

Here's why:

• The "TRIGGER finished" message appeared before "EVENT started"
  
• which means the trigger finished executing before the event even started,
  
• and even worse, the "INSERT finished" appeared before "EVENT finished"
  
• which probably means INSERT t2 (the child) ran before the event had time to INSERT t1 (the parent)
  
• which probably means there was no row in t1 when it hit the COMMIT
  
• which probably explains the "No primary key value for foreign key" error.
  

No, it definitely explains the error :)

But wait, it gets worse!

There's no "EVENT finished" message at all,

• which means the event connection started but never finished
  
• because the event connection was blocked by a "Lock on t1" (Foxhound said so).
  
  
• But wait... no other connection touched t1!
  
• Oh, yes they did... the connection with the CREATE TRIGGER executed "SELECT type FROM t1" in the WHEN clause
  
• and even a read-only SELECT is enough to grab something called a "schema lock" on the table.
  
• A schema lock is a pretty feeble kind of lock,
  
• it doesn't prevent another connection from doing a SELECT,
  
• it doesn't even block an UPDATE,
  
• but a schema lock is strong enough to block ALTER TABLE (because the docs say so),
  
• so... by the very fact that the trigger did a SELECT FROM t1,
  
• the event was blocked before it even got started.
  

Talk about useless!

The event started late, and when it did run, it couldn't do anything.

It's time...

It's time to stop, back up, start over, find another way.

Back up all the way to the point the tables existed but not the CREATE TRIGGER:

(I know, you've been screaming "SHOW ME THE TABLES!" for quite some time now, so here they are.)

CREATE TABLE t1 (
   type            INTEGER NOT NULL PRIMARY KEY,
   CONSTRAINT check_that_t1_type_exists_in_t2 
      CHECK ( type IN ( SELECT type FROM t2 ) ) );

CREATE TABLE t2 (
   id              INTEGER NOT NULL PRIMARY KEY,
   type            INTEGER NOT NULL,
   CONSTRAINT t2_is_a_child_of_t1 
      FOREIGN KEY ( type )  
      REFERENCES t1 );

INSERT t1 VALUES ( 100 );

Constraint 'check_that_t1_type_exists_in_t2' violated: Invalid value in table 't1'
SQLCODE=-1091, ODBC 3 State="23000"

INSERT t2 VALUES ( 1, 100 );

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"

Here's what's going on:

• There is a FOREIGN KEY relationship between t2 (the child) and t1 (the parent)
  
• which means you can't INSERT t2 if there isn't already a single row in t1 with a matching value in type.
  
• There is also a CHECK constraint which says the reverse:
  
• you can't INSERT t1 if there isn't already one or more rows in t2 with a matching value in type.
  

Abort! Abort! I say again, Abort!

Or, if you prefer... Danger, Will Robinson!



How about Catch 22? You can't INSERT t2 before you INSERT t1, and you can't INSERT t1 before you INSERT t2.

From the end user's point of view, it's a perfectly reasonable request.

From a database designer's point of view, it's like those steenking fishhook foreign key relationships, only harder... and sharper... and more painful... like fishhooks in real life.

How about an epiphany?

Here's one: The WAIT_FOR_COMMIT option might not help with INSERT t1...

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

INSERT t1 VALUES ( 100 );

Constraint 'check_that_t1_type_exists_in_t2' violated: Invalid value in table 't1'
SQLCODE=-1091, ODBC 3 State="23000"

but it does let you get past INSERT t2 without an error, at least until you do a COMMIT...

SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';

INSERT t2 VALUES ( 1, 100 );

-- OK so far, but...

COMMIT;

No primary key value for foreign key 't2_is_a_child_of_t1' in table 't2'
SQLCODE=-194, ODBC 3 State="23000"
Line 7, column 1
-- OK so far, but...
COMMIT

The key word is AFTER

Here's another epiphany: Code a CREATE TRIGGER that runs after INSERT t2 finishes, but before the COMMIT, a trigger that does the INSERT t1 so the COMMIT doesn't crash out.

Step by step:

1. Change the CREATE TRIGGER from BEFORE to AFTER,
   
2. don't bother with EXECUTE IMMEDIATE,
   
3. don't bother with CREATE EVENT,
   
4. dump the ALTER TABLE statements so only thing left inside the trigger is the INSERT t1,
   

   CREATE TABLE t1 (
      type            INTEGER NOT NULL PRIMARY KEY,
      CONSTRAINT check_that_t1_type_exists_in_t2 
         CHECK ( type IN ( SELECT type FROM t2 ) ) );
   
   CREATE TABLE t2 (
      id              INTEGER NOT NULL PRIMARY KEY,
      type            INTEGER NOT NULL,
      CONSTRAINT t2_is_a_child_of_t1 
         FOREIGN KEY ( type )  
         REFERENCES t1 );
   
   CREATE TRIGGER trigger_after_insert_t2
      AFTER INSERT ON t2
      REFERENCING NEW AS new_t2
      FOR EACH ROW
      WHEN ( new_t2.type NOT IN ( SELECT type FROM t1 ) )
   BEGIN
      INSERT t1 ( type ) VALUES ( new_t2.type );
   END;
   
5. use SET TEMPORARY WAIT_FOR_COMMIT = 'ON' before your INSERT t2 statements,
   
6. and don't code INSERT t1 outside the trigger... let the trigger do it.
   

   SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'ON';
   INSERT t2 VALUES ( 1, 100 );
   COMMIT;
   INSERT t2 VALUES ( 2, 100 );
   COMMIT;
   INSERT t2 VALUES ( 3, 200 );
   COMMIT;
   INSERT t2 VALUES ( 4, 200 );
   COMMIT;
   SET TEMPORARY OPTION WAIT_FOR_COMMIT = 'OFF';
   

Woohoo! It works!

SELECT * FROM t1;
SELECT * FROM t2;

type
100
200

id,type
1,100
2,100
3,200
4,200

Credits...

This article started life as a Q&A conversation "ALTER TABLE within trigger" on the SQL Anywere Forum.

Volker Barth suggested that "Instead of the "wait_on_commit" option, one might also declare the FK with the CHECK ON COMMIT clause..."

Thomas Duemesnil suggested using an INSTEAD OF trigger instead of... [snork! pun intended]... the traditional AFTER trigger used here.

I take full (dis)credit for the CREATE EVENT suggestion, for which this article is offered as penance.

---