structured query language error when running the code-CodePudding

When I try to run the below code; I received the following error.

CREATE PROCEDURE proc_test
    (IN roger_value INT, dd VARCHAR(100), way_id INT)
LANGUAGE SQL MODIFIES SQL DATA
    INSERT INTO testall
    VALUES
        (000, roger_value, dd, 777, way_id);

CREATE TRIGGER test1
AFTER INSERT ON testall
FOR EACH ROW mode DB2SQL
UPDATE testall
SET     way_out = way_out   1,
        way_in = way_in   1;

CREATE TRIGGER test2
AFTER INSERT ON testall
FOR EACH ROW mode DB2SQL
DELETE FROM testfirst
WHERE (SELECT testfirst.roger_value FROM testfirst) = (SELECT testall.roger_value FROM testall);  


CALL proc_test(999, 'testvalue', 8888) ;


Status:
Failed
Error message
An error occurred in a triggered SQL statement in trigger "schema111.test2".  Information returned for the error includes SQLCODE "-811", SQLSTATE "21000" and message tokens "".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.27.25

I am not sure what is happening after trying many sites but still no answer. Can anyone help?

CodePudding user response：

If I divine your intention correctly, make the following changes:

Use the keyword new to refer to the row being inserted
Fix the syntax

Applying these points makes the delete:

DELETE FROM testfirst
WHERE roger_value = NEW.roger_value;

CodePudding user response：

If the goal is to delete all rows from testfirst having the same ROGER_VALUE value as in the inserted row into testall, then:

CREATE OR REPLACE TRIGGER test2
AFTER INSERT ON testall
REFERENCING NEW AS N
FOR EACH ROW mode DB2SQL
DELETE FROM testfirst F
WHERE F.ROGER_VALUE = N.ROGER_VALUE

If the goal is to update some values in the inserted row only (and not in all table rows), then:

CREATE TRIGGER test1
BEFORE INSERT ON testall
REFERENCING NEW AS N
FOR EACH ROW mode DB2SQL
SET     way_out = N.way_out   1,
        way_in = N.way_in   1