here is my procedure and trigger that activates it

CREATE PROCEDURE update_consumers()
DEFINE max_id INTEGER;
DEFINE max_type CHAR(1);
LET max_id = 0;
LET max_id = (SELECT max(consumer_id) FROM tbl_consumers);
LET max_type = (SELECT c_type FROM tbl_consumers WHERE consumer_id = max_id);
IF max_type = "M" THEN
INSERT INTO tbl_members(consumer_id)
VALUES (max_id);
INSERT INTO tbl_points(consumer_id, p_type, p_point)
VALUES (max_id, "X", 0);
INSERT INTO tbl_points(consumer_id, p_type, p_point)
VALUES (max_id, "P", 0);
END IF
END PROCEDURE ;

--CREATE TRIGGER insert_consumer_types
--INSERT ON tbl_consumers
--AFTER (EXECUTE PROCEDURE update_consumers());

what would happen is the first insert would work successfully
but from the 2nd insert and on, there would be an error stating the unique index constraint has been violated.

upon further checking, tbl_consumers's records are inserted successfully, but tbl_members/tbl_points are not.

is there something i am missing??

Mark