Database state when FOR EACH ROW trigger fires

I have an INSERT trigger that fires on FOR EACH ROW CREATE TRIGGER ins_table_trigINSERT ON table REFERENCING NEW AS new_table_value FOR EACH ROW ( ... ); The table in question has a Primary Key column 'table_id' that is defined as SERIAL. Q1 : What value will new_table_value.table_id (the SERIAL column) have when the trigger fires ? Q2 : Is the row being inserted visible in table if the trigger calls an SPL procedure that does a 'SELECT xxx FROM table ...' ? -- Mark Thornber ==================== E M Thornber CEng MIET Enchanted Systems Limited Software Toolsmiths +44 (0) 1503 272097 Registered in England No: 3595651 Registered Office: Little Garth, Talland Hill Polperro, LOOE, Cornwall PL13 2JL VAT No: GB 717 7967 83

The serial number will be available in new_table_value.table_id but the new row will not yet be visible in the table if the procedure queries for it IB. However, the table_id could be passed into the routine as an argument, and if this is 11.xx and you make the procedure a TRIGGER PROCEDURE then the new_table_value record is available inside the procedure as a global variable. Also, within the procedure you can call DBINFO('sqlca.sqlerrd1') to get the serial number assigned to the new row even in older versions. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Apr 26, 2010 at 6:30 AM, Mark Thornber <mark.thornber@gmail.com>wrote: > I have an INSERT trigger that fires on FOR EACH ROW > > CREATE TRIGGER ins_table_trig> INSERT ON table > REFERENCING NEW AS new_table_value > FOR EACH ROW ( ... ); > > The table in question has a Primary Key column 'table_id' that is > defined as SERIAL. > > Q1 : What value will new_table_value.table_id (the SERIAL column) have > when the trigger fires ? > Q2 : Is the row being inserted visible in table if the trigger calls > an SPL procedure that does a 'SELECT xxx FROM table ...' ? > > -- > Mark Thornber > > ==================== > E M Thornber CEng MIET > Enchanted Systems Limited > Software Toolsmiths > +44 (0) 1503 272097 > > Registered in England No: 3595651 > Registered Office: > > Little Garth, Talland Hill > > Polperro, LOOE, Cornwall > > PL13 2JL > VAT No: GB 717 7967 83 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000e0cd32f5078e8f3048522095a

On 26 April 2010 12:25, Art Kagel <art.kagel@gmail.com> wrote: > The serial number will be available in new_table_value.table_id but the new > row will not yet be visible in the table if the procedure queries for it > IB. However, the table_id could be passed into the routine as an argument, > and if this is 11.xx and you make the procedure a TRIGGER PROCEDURE then the > new_table_value record is available inside the procedure as a global > variable. Also, within the procedure you can call DBINFO('sqlca.sqlerrd1') > to get the serial number assigned to the new row even in older versions. thx - I should have given the version details - IDS10 on Solaris 10 For historical reasons (the db schema is inherited from C-ISAM flat files via Informix SE) I need to check for non-unique values in a pair of columns that cannot have a unique index placed across them. I created the above trigger with matching SPL proc that does (inter alia) <quote> IF EXISTS ( SELECT table_id FROM table WHERE col1 = param1 AND col2 = param2 ) ... </quote> but on an empty table (ie trigger firing on first insert) the IF condition is TRUE - hence my questions. Maybe I don't understand the EXISTS condition behaviour ? -- Mark Thornber ==================== E M Thornber CEng MIET Enchanted Systems Limited Software Toolsmiths +44 (0) 1503 272097 Registered in England No: 3595651 Registered Office: Little Garth, Talland Hill Polperro, LOOE, Cornwall PL13 2JL VAT No: GB 717 7967 83