Help with a new Trigger/Procedure using conditions

[Andretix]

Hello everyone... Let me set the scenario

Im trying to mimick an autonumber type function by creating a trigger that will populate a database field when ever a new record is added to the database. What it should do is read a table holding the next value for the field and update the "details" table with that value....then go back and add 1 to the table it originally retrieved the value from in order to increment for the next time it runs.

There are 2 tables for the main record...

1 table named "license" a 2nd one named "details" Both of these tables are linked by a field called APkey

The field that needs to be updated is on the "details" table new records are always added via the main "license" table.

The problem is I only want this trigger to execute if and only if the "apdefinition" = 1000

Apdefinition is a field on the "license" table

This is what I have so far

CREATE PROCEDURE licno ( lino VARCHAR(9), pakey INTEGER )
DEFINE nexno VARCHAR(9);
DEFINE lkey INTEGER;

LET nexno = (SELECT nextnum FROM newvalue);
LET lkey = (SELECT apkey FROM license WHERE apkey = pakey);

(Here I update the table where the keys are equal. There always will only be only 1 record....at least thats how its supposed to work.)
UPDATE details
SET certnum = nexno
WHERE apkey = lkey;


(Im thinking here is where the additional statements would go....but how do I add additional statements.....ive only done stored procedures with one sql statement.)


END PROCEDURE;

CREATE TRIGGER licno_trig
INSERT ON license
REFERENCING NEW AS new_licno
FOR EACH ROW (EXECUTE PROCEDURE licno( new_licno.definition, new_licno.apkey ));



--------------------------------------

Where do I add the condition to check if apdefinition = 1000 and how do i tell it to do nothing if apdefinition is not = 1000.

I think I have most of it down.....just stuck on these other items.

Please help

 

[vpshriyan]

Hi,

You may precede a triggered action with a WHEN clause to make the action dependent on the outcome of a test.

CREATE TRIGGER licno_trig
INSERT ON license
REFERENCING NEW AS new_licno
FOR EACH ROW WHEN(new_licno.definition = 1000 )
(EXECUTE PROCEDURE licno( new_licno.definition, new_licno.apkey ));

CREATE PROCEDURE licno ( lino VARCHAR(9), pakey INTEGER )
DEFINE nexno VARCHAR(9);
--DEFINE lkey INTEGER;

LET nexno = (SELECT nextnum FROM newvalue);

-- following LET statement is redundant, as pakey is passed as one of the parameter to SP.
-- LET lkey = (SELECT apkey FROM license WHERE apkey = pakey);

UPDATE details
SET certnum = nexno
WHERE apkey = pakey;

UPDATE newvalue SET nextnum = nextnum + 1

END PROCEDURE;

Regards,
Shriyan
"You can achieve the most when you don't care who gets the credit"