Re: Trigger that acts on referencing table

Nigel Gall wrote: } } At 09:40 PM 8/13/97 EDT, you wrote: } } } }Hi folks, } } } }I'd like to incorporate the following into my table designs, but it } }doesn't appear that Informix supports the capability: } } } }Table XYZ ( } } <columns>... } } modified_datetime datetime year to second } }) } } } }Each time a row is inserted or updated, I would like to have a trigger } }automatically update the modified_datetime so that I always know when } }a row was last changed. } } } }Informix triggers don't seem to let me update the time in this table } }on an insert (I can update the time in an update trigger if that column } }is not referenced in the update). } } } }If anyone has needed to do this, how did you implement it? Is there a } }better way to provide this sort of information about the row? } } } } Is it possible to use the DEFAULT clause of the create table statement? } That way you won't have to worry about embedding extra code to store the } modified_datetime value, and also you can use other utilities (like dbload, } isql, dbaccess) to insert rows without added headache. Sorry I don't have } the manuals close by; maybe someone else cane give you the exact syntax, or } you can check your I-SQL Reference manual. } } }Thanks, } }Lorrie Gagnon Unfortunately, the default clause only inserts a value when a row is being inserted into the table. If that row is subsequently updated, the time stamp value won't be updated without a trigger. I'd suggest creating a view with a select statement only on the rows that the user should see. For the timestamp, etc use a trigger to update the time stamp. Revoke permissions on the table from the users, so they can't directly attempt a update of that time stamp column -- Uday Shankar Derivative Products Group Republic National Bank of New York uday@dpg.rnb.com (212) 525-8000