Problem With Update trigger [CORE1415]

[firebird-automations]

Submitted by: Marcos Thomaz (thomazs)

I have in my database the structure:

CREATE TABLE PAGAMENTOCLIENTES ( ID DM_ID NOT NULL /* DM_ID = INTEGER */, CAIXA DM_ID NOT NULL /* DM_ID = INTEGER */, DATA DM_DTNASC NOT NULL /* DM_DTNASC = DATE */, CLIENTE DM_ID NOT NULL /* DM_ID = INTEGER */, VALOR DM_VALORES NOT NULL /* DM_VALORES = NUMERIC(15,3) */, USUARIO DM_ID NOT NULL /* DM_ID = INTEGER */ );

And a trigger with the structure:

SET TERM ^ ; CREATE TRIGGER PAGAMENTOCLIENTES_AIUD0 FOR PAGAMENTOCLIENTES ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0 AS begin IF (DELETING OR UPDATING) THEN UPDATE CLIENTES SET DEBITO = COALESCE(DEBITO,0) + OLD.VALOR WHERE ID = OLD.CLIENTE; IF (INSERTING OR UPDATING) THEN UPDATE CLIENTES SET DEBITO = COALESCE(DEBITO,0) - NEW.VALOR WHERE ID = NEW.CLIENTE; end ^ SET TERM ; ^

Then, I add a field with the command:

ALTER TABLE PAGAMENTOCLIENTES ADD VALORANTERIOR DM_VALORES;

After this, I try to update my trigger with the command:

SET TERM ^ ; CREATE TRIGGER PAGAMENTOCLIENTES_AIUD0 FOR PAGAMENTOCLIENTES ACTIVE AFTER INSERT OR UPDATE OR DELETE POSITION 0 AS begin IF (DELETING OR UPDATING) THEN UPDATE CLIENTES SET DEBITO = COALESCE(DEBITO,0) + OLD.VALOR WHERE ID = OLD.CLIENTE; SELECT DEBITO FROM CLIENTE WHERE ID = NEW.CLIENTE INTO NEW.VALORANTERIOR; IF (INSERTING OR UPDATING) THEN UPDATE CLIENTES SET DEBITO = COALESCE(DEBITO,0) - NEW.VALOR WHERE ID = NEW.CLIENTE; end ^ SET TERM ; ^

In compilation, show the message:

"This column cannot be updated because it is derived from an SQL function or expression. attempted update of read-only column."

And after this, the table is updated to ReadOnly. It came back to normal state, when I drop the field VALORANTERIOR, and create it again. I try execute backup / restore (gbak), gfix, re-created my database (complete) ,but nothing resolve the problem.

I use Firebird 2.0

[firebird-automations]

Commented by: @dyemanov

You select value into NEW.VALORANTERIOR. Such an assignment is prohibited for AFTER-triggers.

[firebird-automations]

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Won't Fix [ 2 ]

[firebird-automations]

Modified by: @pcisar

status: Resolved [ 5 ] => Closed [ 6 ]

[firebird-automations]

Modified by: @pcisar

Workflow: jira [ 12812 ] => Firebird [ 13908 ]

[pavel-zotov]

Why this ticked has mark "Won't fix" ?

set bail OFF;
set blob all;
set list on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
create table test(id int constraint test_pk primary key, f01 int);

-- Attempt to create `AFTER` trigger must fail if `new.` is changed
set term ^;
-- Statement failed, SQLSTATE = 42000
-- attempted update of read-only column TEST.ID
create trigger trg_test for test after insert or update as
begin
    new.f01 = rand() * 1000;
end ^
set term ;^
rollback;

-- connect 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey'; --------------- [ 1 ]
show trigger;

insert into test(id) values(1); ----------------- [ 2 ]
commit;
alter table test
    drop f01
   ,add f01 int
;
insert into test(id) values(2);

Until reconnect is performed (marked above as [ 1 ]), we have to drop / create field in alter table statement Otherwise statement [ 2 ] will fail with SQLSTATE = 42000 / attempted update of read-only column "PUBLIC"."TEST"."F01"

It's obvious that this is inconvenient "workaround".