Обсуждение: [GENERAL] Help with Trigger
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
On 12/28/2016 07:06 PM, Clifford Snow wrote: > 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 What is the actual error message? > 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; In the above you are checking whether the changes table has the user_id and if does not then creating a new user in the user table below. Not sure how they are related, but from the description of the error it would seem they are not that tightly coupled. In other words just because the user_id does not exist in changes does not ensure it also absent from the table user. Off the top of head I would say the below might be a better query: SELECT INTO commits * FROM user WHERE user_id = NEW.user_id; Though it would help the debugging process if you showed the complete schema for both the changes and user tables. > 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 > > > -- > @osm_seattle > osm_seattle.snowandsnow.us <http://osm_seattle.snowandsnow.us> > OpenStreetMap: Maps with a human touch -- Adrian Klaver adrian.klaver@aklaver.com
Thank you for your suggestion which solved the problem. Much better solution that what I was trying to accomplish. Much smaller table to query since it only has one entry per user.
Clifford
On Wed, Dec 28, 2016 at 8:12 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 12/28/2016 07:06 PM, Clifford Snow wrote: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
What is the actual error message?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;
In the above you are checking whether the changes table has the user_id and if does not then creating a new user in the user table below. Not sure how they are related, but from the description of the error it would seem they are not that tightly coupled. In other words just because the user_id does not exist in changes does not ensure it also absent from the table user. Off the top of head I would say the below might be a better query:
SELECT INTO commits * FROM user WHERE user_id = NEW.user_id;
Though it would help the debugging process if you showed the complete schema for both the changes and user tables.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
--
@osm_seattle
osm_seattle.snowandsnow.us <http://osm_seattle.snowandsnow.us>
OpenStreetMap: Maps with a human touch
--
Adrian Klaver
adrian.klaver@aklaver.com