I'm trying to write a trigger (my first) to update another table if the user_id is new. But I'm getting a index exception that the user_id already exists. I'm picking up data from another feed which gives provides me with changes to the main database.
--
what I have is
CREATE OR REPLACE FUNCTION add_new_user()
RETURNS TRIGGER AS
$BODY$
DECLARE
commits RECORD;
BEGIN
SELECT INTO commits * FROM changes WHERE user_id = NEW.user_id;
IF NOT FOUND
THEN
INSERT INTO user (user_name, user_id, change_id, created_date)
VALUES(NEW.user_name, NEW.user_id, NEW.change_id, NEW.created_date);
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;
CREATE TRIGGER add_new_user_trigger
BEFORE INSERT ON changes
FOR EACH ROW
EXECUTE PROCEDURE add_new_user();
I hoping for some recommendations on how to fix or at where I'm going wrong.
Thanks,
Clifford
