Re: FW: Infromix question - The acursed recursive trigger again!

CSC CIS wrote: > > Am passing the following inquiry along for a co-worker... > *********************************** > > I would like to know if it is possible to create an insert trigger for > the following table. --- SNIP --- > If the count that is returned is 0, I would like to insert a 1 in the > sequence column with the ebp_id, dlp_id, and client columns being the > same as the above select. If the returned value is greater than 0, I > would like to added 1 to that value and insert that new value in the > sequence column with the ebp_id, dlp_id, and client columns being the > same as the above select. > > My experience in trying to added this trigger has ended with the > following Informix error, 747 "Table or column matches object referenced > in triggering statement. This error is returned when a triggered SQL > statement acts on the triggering table, or when both statements are > updates and the column being updated in the triggered action is the same > as the column being updated by the triggering statement." My question is > this. Can the results that I desire be accomplished with a > trigger/stored procedure combination? If so what would be the syntax of > the trigger and stored procedure? -- (In the future, please edit out the line-drawing codes from your post.) Matt, this has been discussed before and will continue to be argued until Informix removes this restriction. In a trigger action, you cannot reference the column that caused the trigger to fire. In an insert trigger, it makes sense (or is at least consistent with Informix thinking) to forbid you to enter a new row in the course of the trigger action; it could cause an infinite loop of insert triggers to fire. If you try to hide your action by calling a stored procedure from the trigger action code you will still get the same 747 jumbo error. It knows what you're up to! -- Jake (Lost in thought and won't ask for directions) . . _..-'( )`-.._ ./'. '||\\\\. }\\_/{ .//||` .`\\. ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\. ./'..|'.|| |||||\\`````` \\,@,/ ''''''/||||| ||.`|..`\\. ./'.||'.|||| ||||||||||||. ||| .|||||||||||| ||||.`||.`\\. /'|||'.|||||| ||||||||||||{ | }|||||||||||| ||||||.`|||`\\ '.|||'.||||||| ||||||||||||{ | }|||||||||||| |||||||.`|||.` '.||| ||||||||| |/' ``\\||`` | ''||/'' `\\| ||||||||| |||.` |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\| V V V }' `\\ /' `{ V V V \\ \\ \\ V / / / +-----------------------------------------------------------+ | Impeccable Logic: A thought process which successfully | | resists chicken bites | +-----------------------------------------------------------+