hi,
i've create this trigger:

create or replace TRIGGER SITE_ID_POST_UPDATE
AFTER UPDATE OF
SITE_ID
ON SITE
REFERENCING
NEW AS NEW
OLD AS OLD
FOR EACH ROW
DECLARE VAR_COD_CONV CHAR(3);
CAR1 CHAR(1);
CAR2 CHAR(1);
CAR3 CHAR(1);
CONTA NUMBER(11);
Begin
SELECT COUNT(*) INTO CONTA FROM AFM_COD_CONV WHERE SITE_ID=:NEW.SITE_ID;
IF CONTA=0 THEN
SELECT COUNT(*) INTO CONTA FROM AFM_COD_CONV;
IF CONTA=0 THEN
CAR1:='0';
CAR2:='0';
CAR3:='0';
ELSE
SELECT MAX(COD_CONV) INTO VAR_COD_CONV FROM AFM_COD_CONV;
CAR3 := SUBSTR(VAR_COD_CONV,3,1);
CAR2 := SUBSTR(VAR_COD_CONV,2,1);
CAR1 := SUBSTR(VAR_COD_CONV,1,1);
IF CAR3='Z' THEN
IF CAR2='Z' THEN
CAR1:=SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ 0',INSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',CA R1)+1,1);
CAR2:='0';
ELSE
CAR2:=SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',INSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',CAR 2)+1,1);
END IF;
CAR3:='0';
ELSE
CAR3:=SUBSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ',INSTR('0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',CAR 3)+1,1);
END IF;
END IF;
INSERT INTO AFM_COD_CONV (COD_CONV,SITE_ID) VALUES (CAR1||CAR2||CAR3,:NEW.SITE_ID);
ELSE
UPDATE AFM_COD_CONV SET SITE_ID=:NEW.SITE_ID WHERE SITE_ID=:OLD.SITE_ID;
END IF;
End;


It is correct....in tab SITE when I update the col SITE_ID inser automaticcaly into col COD_CONV of tab AFM_COD_CONV three char progressive (000, 001,...A00,....ZZZ)
Now I've this problem:
In tab SITE there is also col county_id (province) and in another tab COUNTY there are col county_id and col region(char(1)....A, B, C....)
When I update the site_id of site the trigger would have to verify which region corresponds to that province and insert automatic in cod_conv of AFM_COD_CONV four character (X000, A001, BC02,.......) where the first char is the region.
It's possible and how??
Thanks
Raf