Re: Help with Trigger

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
>

--
@osm_seattle
osm_seattle.snowandsnow.us
OpenStreetMap: Maps with a human touch