You should be using BEFORE INSERT triggers if you want to update the
value in the database. Those columns are read-only in an AFTER INSERT
trigger, causing the "attempted update of read-only column." message.

You should be using NEW, so omitting that cause the errors "Column does
not belong to the referenced table"

Chuck Belanger wrote:

> Hello:
>
> I recently upgraded a desktop project from FB 1.5 to FB 2.0 and when
> running a script in IB_Expert to create the new DB, I have 7 triggers
> out of 62 which get similar error messages. I have pasted the error
> messages for 3 of them since they represent the type of triggers and errors.
>
> I did read the FB FAQ about this topic, which only left me scratching my
> head. These are not computed fields, but I do use NEW and OLD. I tried
> with/without NEW/OLD adding the name of the table as a prefix to the
> field. Nothing seems to work and typically causes the "Column does not
> belong to the referenced table" error.
>
> I suspect that I missing some fundamental understanding and would really
> appreciate any advice.
>
> Thank you,
>
> Chuck Belanger
>
>
> For the heck of it, I tried adding Update to the first trigger:
>
> IF (NEW.ORIG_CV_ID IS NULL) THEN
> update Custom_View cv set cv.ORIG_CV_ID = cv.CV_ID;
>
> and this passed the script test, but I'm really not sure this is correct
> and am wondering if it will work only on the record that is being Inserted?
>
>
> SET TERM ^ ;
>
> /*******************************************************************************
> The next statement causes the following error:
>
> This column cannot be updated because it is derived from an SQL function
> or expression.
> attempted update of read-only column.
> *******************************************************************************/
> CREATE TRIGGER CUSTOM_VIEW_AI0 FOR CUSTOM_VIEW
> ACTIVE AFTER INSERT POSITION 0
> AS
> begin
> /* auto fill the Orig_CV_ID field when adding a new item */
> IF (NEW.ORIG_CV_ID IS NULL) THEN
> New.ORIG_CV_ID = New.CV_ID;
> end
> ;
>
> /*******************************************************************************
> The next statement causes the following error:
>
> This column cannot be updated because it is derived from an SQL function
> or expression.
> attempted update of read-only column.
> *******************************************************************************/
> CREATE TRIGGER CV_TABLE_COLUMNS_AI0 FOR CV_TABLE_COLUMNS
> ACTIVE AFTER INSERT POSITION 0
> AS
> begin
> /* auto fill the Old_ID field when adding a new item */
> IF (NEW.OLD_COLUMN_ID IS NULL) THEN
> NEW.OLD_COLUMN_ID = NEW.CV_COLUMN_ID;
> end
> ;
>
> /*******************************************************************************
> The next statement causes the following error:
>
> Column does not belong to referenced table.
> Dynamic SQL Error.
> SQL error code = -206.
> Column unknown.
> OLD_TABLE_ID.
> At line 6, column 19.
> *******************************************************************************/
> CREATE TRIGGER CV_TABLE_AI0 FOR CV_TABLE
> ACTIVE AFTER INSERT POSITION 0
> AS
> begin
> /* auto fill the Old_ID field when adding a new item */
> IF (OLD_TABLE_ID IS NULL) THEN
> OLD_TABLE_ID = CV_TABLE_ID;
> end
> ;

--
Mark Rotteveel