RE: After Insert Trigger
>----------
Art S. Kagel wrote:
>Jon Brown wrote:
>> We are developing a large client server system with an Informix back end.
> >Each of the tables in the database has a lastupdated and bywhom field which
> w>e had invisioned setting by use of an 'after Insert/Update' Trigger.
> (i.e.)
>> CREATE TRIGGER post_insert>> INSERT ON tablename
>> REFERENCING NEW AS NEW_INS
>> FOR EACH ROW ( NEW_INS.lastupdated = CURRENT;
>> NEW_INS.bywhom = USER);
>>
>> It would appear that it is impossible to update an informix table with a
>> trigger that is on the same table
>> Is this the case or are we just being stupid?
JL: You cannot alter the data inserted explicitly by the user (see below); you aren't being stupid.
>You can instead alter to table to include DEFAULT clauses for each of
>these columns:
>
>alter table tablename modify (lastupdated datetime year to fraction(2)>default CURRENT), modify (bywhom char(8) default USER);
>
>That will take care of your requirements
JL: That on its own is not sufficient. You also have to ensure that the INSERT statement
does not specify the LastUpdated and ByWhom columns. This typically means either you
INSERT through a view which excludes those columns:
CREATE VIEW ins_tablename (col1, ..., colN) AS
SELECT col1, ..., colN
FROM tablename;
Note that colN excludes LastUpdated and ByWhom... Or you specify the column list in the
INSERT statement:
INSERT INTO tablename(col1, ..., colN)
VALUES (val1, ..., valN);
This allows the DEFAULT for the columns to take effect; if a value is specified for the column,
that value is inserted, regardless of the defaults.
You can intercept the LastUpdated and ByWhom columns in an UPDATE trigger, but again,
this is only effective if you avoid setting the columns explicitly in the UPDATE statement.
That rules out:
UPDATE tablename
SET * = (record.*)
Yours,
Jonathan Leffler (jleffler@visa.com) #include <dejanews.h>