Does anyone have an example trigger which updates ...
# sql
e
Does anyone have an example trigger which updates a newly inserted record using some of the new record data? cc: @User
s
This sounds so recursive, I'm a little confused as to what you mean
e
Definitely could be recursive. Our example would be: field A + field B = field C The user inputs field A and field B And we need to calculate field C
And we have it only running on insert - not update.
s
Copy code
sql
create function public.handle_new_user()
returns trigger as $$
begin
    new.full_name = new.first_name || ' ' || new.last_name
    insert into public.profiles (user_id, full_name)
    values (new.id, new.full_name);
    return new;
end;
$$ language plpgsql security definer;
There is an example of what the trigger function would look like while combining two new data being sent into to create one
e
trying this now
can't get it to work. tried a dozen different variations for our use case (simplest versions of it) and ran into various errors. most common error is caused by the 'insert into'. It looks to be trying to create a duplicate record vs. updating the record. I tried to change it to an update - but I can't get that to work or actually pass an updated value
s
paste your code here and lets work from that
I'm going to watch a movie now, but when I'm done I'll have a look at your code
e
CREATE OR REPLACE FUNCTION public.update_res_info_f () returns trigger as $$ BEGIN     new.platform_payout = new.parking_fee * 2 ;     insert into public.reservations (id , platform_payout)       values (new.id , new.platform_payout);   RETURN new; END; $$ language plpgsql security definer; CREATE TRIGGER t5 AFTER INSERT ON reservations   FOR EACH ROW EXECUTE PROCEDURE update_res_info_f();
s
ok I got this working now, you need to make some changes to this as you are updating a record after it has been inserted
Copy code
sql
CREATE OR REPLACE FUNCTION public.update_res_info_f () 
returns trigger as $$
BEGIN
    new.platform_payout = new.parking_fee * 2 ;
    update public.reservations 
    set platform_payout = new.platform_payout
    where id = new.id;
    RETURN new;
END; 
$$
language plpgsql security definer;

CREATE TRIGGER t5
AFTER INSERT ON reservations
FOR EACH ROW EXECUTE PROCEDURE update_res_info_f();
I've tested this in one of my projects and it works as expected.