Hi,
 
I've hit an issue which has me stumped... I've written a stored procedure which generates some SQL and then does an EXECUTE STATEMENT on the SQL it creates. That SQL amounts to an update statement: a snippet of code:
 
  /* we can finally build our Update statement and execute it... */
  SQL = 'UPDATE ' || TABLENAME || ' SET ' || SETCLAUSE
     || 'WHERE ' || PKFIELDNAME ||' = ' || PKFIELDVALUE;
  EXECUTE STATEMENT SQL;
 
This all works correctly once the record has been created. Now for the problem...
 
I need this procedure to run after the record has been inserted. So you'd thing that would be straight forward enough, something like:
 
SET TERM ^ ;
CREATE TRIGGER AIU FOR V_MY_VIEW ACTIVE
AFTER INSERT OR UPDATE POSITION 0
AS

BEGIN 
     EXECUTE PROCEDURE P_MY_PROC(....);

END^
SET TERM ; ^
 
But the record is not being updated, so I wrote a small debug chunk to see if I could grab the "NEW" primary key:
 
SET TERM ^ ;
ALTER TRIGGER AIU ACTIVE
AFTER INSERT OR UPDATE POSITION 0
AS

DECLARE VARIABLE IDCheck BigInt;
BEGIN 
  SELECT x.ID FROM MY_TABLE x WHEREe x.ID = NEW.ID into :IDCheck;
  SendDebug(NEW.ID || ' IDCheck: ' || COALESCE(IDCheck, 'NULL!'));

END^
SET TERM ; ^
 
FWIW, V_MY_VIEW is based on table MY_TABLE and ID is the primary key; SendDebug is like the GExperts SendDebug.
 
So anyway, when I tested it, the NEW.ID is what I expected, but the re-queried value into IDCheck is null. In other words the record that was just inserted cannot be selected. How is that possible? The trigger itself by definition is running within the same context as the transaction that inserted the row so I am stumped why I cannot SELECT the value. It is like that record is in some limbo state in the AFTER INSERT. Obvious I can't change the values in AFTER INSERT, but I also cannot query out the record either?!?! And of course I can't call this on BEFORE INSERT since it hasn't been inserted yet, so nothing to Update.
 
Is it possible that this is some wierd quirky behaviour related to the fact that my trigger is on a view (updateable view obviously), but I am querying the table directly? FWIW, I tried as well doing my SELECT on the view with the same results.
 
Can anyone shed some light on what's happening here?
 
cheers,
-randall sell

[Non-text portions of this message have been removed]