Re: triggers using sequence to compile

ypoizeau_at_ifrance.com (yoann) wrote in message news:<70be4d67.0304020201.8dd31d4_at_posting.google.com>...
> i have modify a table and some of my triggers have becomed invalid so
> i have compiled THEM using the
> DBMS_UTILITY.COMPILE_SCHEMA(schema_name); function
> but know i have a problem. when i use sequence in a trigger, the
> trigger doesn't work anymore
> MY TRIGGER:
> CREATE TRIGGER COM_GROUP_INC BEFORE INSERT
> ON COM_GROUPS
> FOR EACH ROW
> DECLARE
> next_number_of_group_id NUMBER;
> BEGIN
> IF :new.GROUP_ID=0 THEN
> BEGIN
> next_number_of_group_id := 0;
> END;
> ELSE
> BEGIN
> SELECT COM_GROUP_AUTOINC.NEXTVAL INTO next_number_of_group_id
> FROM dual;
> :new.GROUP_ID:=next_number_of_group_id;
> END;
> END IF;
> END;
>
> MY SEQUENCE:
> CREATE SEQUENCE COM_GROUP_AUTOINC INCREMENT BY 1
> START WITH 2 MAXVALUE 1.0E28 MINVALUE 2 NOCYCLE
> CACHE 20 NOORDER;
>
> Someone can help me?
> Thanks for all;

Yoann, what error message is any are you getting or are the results just wrong?

Question: Why are you testing for the group_id being equal to zero? If the inserting application did not provide the column on the insert I would expect it to be NULL. Are the applications inserting zero by default? If so then I think your IF logic is reversed in that for a zero value you would substitute a sequence generated value and for a non-zero value you would wnat to just accept the input value.

More information might be helpful.

HTH -- Mark D Powell -- Received on Wed Apr 02 2003 - 23:03:32 CEST