Helene

Thanks for your reply and info. The execute statement did the trick

> You are making a lot of mistakes here...where to start? ;-)
>
> Probably the first place is to tell you that you cannot use parameters or variables to refer to database object identifiers.

Thought so

> The third problem is that you are trying to refer to the values of the columns directly in a trigger. That doesn't work. In a trigger, you can only read/change the values via the NEW.* and OLD.* context variables.
>
> The fourth problem seems to be that you are expecting the current record to be updatable from an AFTER trigger. It is not. The current record is read-only in an AFTER trigger.

I am updating another table, so this does not apply.

> Note, also, whenever you access a local variable in PSQL (which includes the code where you would be composing a string for EXECUTE STATEMENT) you *do not* apply the colon prefix, e.g.

Thanks for the tip, I would have tried with the colon prefix.

Just for reference here is the update trigger:

CREATE OR ALTER trigger hotel_bookings_detail_au0 for hotel_bookings_detail
active after update position 0
AS
declare variable OldUnit var15 ;
declare variable NewUnit var15 ;
declare variable SqlStmt var160;

begin
IF (old.STOCK_ITEM = -1) THEN
begin
OldUnit = 'U' || OLD.UNIT_CODE;
SqlStmt = 'UPDATE room_status SET ' || oldunit || ' = 0 where DT between ''' || Cast(old.dt_from as date) || ''' and ''' || Cast(old.dt_to-1 as date) || '''';
Execute STATement (sqlstmt);
end
IF (new.STOCK_ITEM = -1) THEN
begin
NewUnit = 'U' || New.UNIT_CODE;
SqlStmt = 'UPDATE room_status SET ' || newunit || ' = ' || new.item_status || ' where DT between ''' || Cast(new.dt_from as date) || ''' and ''' || Cast(new.dt_to-1 as date) || '''';
Execute STATement (sqlstmt);
end
end

Again thanks a lot


Cornie