Re: TRIGGER AND ERROR 747

Art S. Kagel wrote: > > RL wrote: > > > > Hi! > > > > I want to modify the data of a table (TABLE_A) by using > > a trigger with insert event on this table (TABLE_A) > > > > but Informix send me 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 that is updated in the > > triggered > > > > action is the same as the column that the triggering statement updates. > > > > What I can do to do it with out receive this error message ? > > > > Could you help me ? > > You cannot get around this in versions before 7.30. The Informix > engineers who designed the trigger interface couldn't imagine why one > would want to adulterate the data a user explicitely inserted so they > disallowed such things. The restriction was lifted in 7.30. > > Art S. Kagel According to the documentation for 7.3 (I'm still running 7.23) the lifting or the restriction is partial: you may only update fields that were _not_ explicitly set by the insert statement. From the manual: "If the trigger event is an INSERT statement, the triggered action can be an UPDATE statement that references a column in the triggering table. However, this column cannot be a column for which a value was supplied by the trigger event. If the trigger event is an INSERT, and the triggered action is an UPDATE on the triggering table, the columns in both statements must be mutually exclusive. For example, assume that the trigger event is an INSERT statement that inserts values for columns cola and colb of table tab1: INSERT INTO tab1 (cola, colb) VALUES (1,10) Now consider the triggered actions. The first UPDATE statement is valid, but the second one is not because it updates column colb even though the trigger event already supplied a value for column colb. UPDATE tab1 SET colc=100; --OK UPDATE tab1 SET colb=100; --ILLEGAL " Cheers Gabor Heppes