Does anyone have an example trigger which updates ...
# sqle
erik_flywheel
11/19/2021, 8:04 PMDoes anyone have an example trigger which updates a newly inserted record using some of the new record data?
cc: @User
s
silentworks
11/19/2021, 8:05 PMThis sounds so recursive, I'm a little confused as to what you mean
e
erik_flywheel
11/19/2021, 8:07 PMDefinitely 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
erik_flywheel
11/19/2021, 8:08 PMAnd we have it only running on insert - not update.
s
silentworks
11/19/2021, 8:13 PM 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;silentworks
11/19/2021, 8:14 PMThere is an example of what the trigger function would look like while combining two new data being sent into to create one
e
erik_flywheel
11/19/2021, 8:17 PMtrying this now
erik_flywheel
11/19/2021, 8:32 PMcan'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
silentworks
11/19/2021, 8:34 PMpaste your code here and lets work from that
silentworks
11/19/2021, 8:34 PMI'm going to watch a movie now, but when I'm done I'll have a look at your code
e
erik_flywheel
11/19/2021, 8:36 PMCREATE 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
silentworks
11/20/2021, 1:51 PMok I got this working now, you need to make some changes to this as you are updating a record after it has been inserted
silentworks
11/20/2021, 1:53 PM 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();