help on how to resolve error -747 in a trigger

[Andretix]

Here is my trigger and procedure

both were created successfully on the database via dbaccess.

This trigger is meant to run when a new record is added to the table. All I want it to do it update a separate field on the same table that the trigger is on.

Can this not be done?

CREATE PROCEDURE buff_licno1 (pakey integer)
DEFINE nexno integer;
DEFINE lkey INTEGER;
define testkey integer;

set debug file to "/scratch/trig.trace";
TRACE "begin trace";
TRACE ON;

LET nexno = (SELECT nextvalu from buff_seq1);

LET testkey = (SELECT Max(cobcoo.apkey) AS MaxOfapkey FROM cobcoo);

UPDATE cobcoo SET trcerti = nexno where apkey = testkey;

update buff_seq1 set nextvalu = nextvalu +1;

TRACE OFF;
END PROCEDURE;


CREATE TRIGGER licno_trig1
INSERT ON cobcoo
REFERENCING NEW AS new_licno1
FOR EACH ROW (EXECUTE PROCEDURE buff_licno1(new_licno1.apkey));

 

[vpshriyan]

Hi,

The triggered and triggering table are the same base table in your case, which is disallowed or prohibited.

Alternatively, if acceptable, you may think of altering cobcoo.trcerti to SERIAL data type field, which inturn able to generate sequence number. In insert sql statement supply 0 (zero) as place-holder data for the generation of next sequence number.

To find out the sequence number generated execute sql:
SELECT DBINFO('sqlca.sqlerrd1') FROM systables WHERE tabid=1 ;

The above select statement is equivalent to SP:
CREATE PROCEDURE GetSerialNumber() RETURNING integer;
RETURN DBINFO('sqlca.sqlerrd1');
END PROCEDURE

To call the SP execute SQL:
EXECUTE PROCEDURE GetSerialNumber()

Regards,
Shriyan
"It is simple to make things complex, but complex to make things simple."