Re: Informix Triggers (fwd)

On Thu, 12 Feb 1998, Jon Brown wrote: > Jonathan and Art, > > Thanks very much for the replies to my newsgroup message about > Insert/Update triggers on Informix tables. > The default setting on the table columns that you suggested have worked > beautifully for the initial insert of a record and my updated and bywhom > columns are being set correctly. > > I noticed from your replies that I should be able to update these > columns again in an update trigger. Therefore I created the following : > > DROP TRIGGER trg_sm_csresults; > CREATE TRIGGER trg_sm_csresults> UPDATE ON sm_csresults > REFERENCING NEW AS post_upd OLD AS pre_upd > FOR EACH ROW(UPDATE sm_csresults SET(cs_lastupdate, cs_bywhom) = > (CURRENT, USER) > WHERE post_upd.cs_id = pre_upd.cs_id); ^^^^^^^^ This should reference the actual table, not post_upd, since it is not the in-process record that you are updating but the already updated record in the actual table (a difference between ANSI compliant triggers like Informix's and non-conforming triggers like Sybase's). This is an identity if cs_id has not been updated and will cause the cs_lastupdate & cs_bywhom columns to be modified on ALL ROWS in the table. But that is not the problem causing the SQLCODE = -747 (SQLSTATE = S1000) error. Read on... > Whenever I try "UPDATE sm_csresults SET cs_result = 'P' WHERE cs_id = > 9999;" I get the same result. Namely the following database error: > > SQLSTATE = S1000 : Table or Column matches object referenced in > triggering statement The problem is that since the trigger definition did not specify a column list the triggered update statement inside of the trigger itself triggered another nested instance of the trigger and in that triggering statement you were updating the two columns (cs_lastupdate, cs_bywhom) being updated by the triggered statement, a no-no. If you try this on a database with no logging (or a temp table created without log) so the transaction cannot rollback and look at the data after the SQLCODE = -747 (SQLSTATE = S1000) error you will find that one row was indeed updated and its cs_lastupdate & cs_bywhom columns have been properly set but if the original update specified multiple rows the rest have not been affected. In a logged table the error forces a rollback and you do not see this. The solution is to make the trigger specific to all of the columns that you may update leaving out cs_lastupdate & cs_bywhom (you may also exclude any key fields that you treat as inviolate). So for example: CREATE TRIGGER trg_sm_csresultsUPDATE FOR cs_result, cs_otherstuff ON sm_csresults REFERENCING NEW AS post_upd OLD AS pre_upd FOR EACH ROW(UPDATE sm_csresults SET(cs_lastupdate, cs_bywhom) =(CURRENT, USER) WHERE sm_csresults.cs_id = pre_upd.cs_id); ^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Note the comparison here! Art S. Kagel, kagel@bloomberg.com