Create Trigger For Update another row automatically on same table using postgresql

Question

I want to create a trigger that can update another row on the same table on PostgreSQL.

if i run the query like these:

UPDATE tokens
SET amount = (SELECT amount FROM tokens WHERE id = 1)
where id = 2

these result that i expected.

description:

i want to set field amount on a row with id:2, where the amount value is from query result on a subquery, so the amount value on id:2 is same with id:1

Hopefully, with this created trigger, i can do update amount value on id=1 so the amount value on id:2 is same with id:1

Before update result:

id | amount|
1  | 200   |
2  | 200   |

When i update the amount value on id:1 to 100 on, so the amount value on id:2 become 100

After update result:

id | amount|
1  | 100   | 
2  | 100   |

Update for my temporary solution: i just create the UDF like these

CREATE FUNCTION update_amount(id_ops integer, id_mir integer) returns boolean LANGUAGE plpgsql AS $$
BEGIN
 UPDATE tokens SET amount = (SELECT amount FROM tokens WHERE id = id_ops) WHERE id = id_mir;
 RETURN 1;
END;
$$;

description:

id_ops: id where the amount i always update
id_mir: id where the amount automatically update after i update the amount with id_ops

Example of using my written UDF to resolve my problem:

I update the amount of id: 1 to 2000. The amount of id: 2 not updated to 2000
When i run query select update_amount(1,2);
The amount of id: 2 will same with amount on id: 1

I Need a trigger on PostgreSQL to automate or replace the function of UDF that i wrote

Thank you for your comment, i have a difficulty to convert my UDF i wrote to trigger, so i can replace my temporary solution. Regards — Gekif
– Gekif, Commented Nov 1, 2019 at 11:09
Is the row you want to update the one you just inserted or a different one? — Laurenz Albe
– Laurenz Albe, Commented Nov 1, 2019 at 11:20
Hi the row that I always update is id 1, when I update the amount value on id 1, the amount value on id 2 mirror from updated amount value id 1 — Gekif
– Gekif, Commented Nov 1, 2019 at 13:45
Is the general condition always the case that you want id N+1 when update id N is updated? What would happen for "update tokens set amount = 10 where id = 3;"? or even "update tokens set amount = 50 where id = 2;"? — Belayer
– Belayer, Commented Nov 1, 2019 at 17:01

Belayer · Accepted Answer · 2019-11-02 05:20:27Z

What you want to do it not really that difficult, I'll show you. But first: This is a very very bad idea. In fact bad enough that some databases, most notable Oracle, throw an exception if try it. Unfortunately Postgres allows it. You essentially create a recursive update as you are updating the table that initiated the trigger. This update in turn initiates the trigger. Without logic to stop this recursion you could update every row in the table.
I assume this is an extract for a much larger requirement, or perhaps you just want to know how to create a trigger. So we begin:

-- setup
drop table if exists tokens;
create table tokens( id integer, amount numeric(6,2));
-- create initial test data
insert into tokens(id, amount)
  values (1,100), (2,150.69), (3,95.50), (4,75), (5,16.40);

Now the heart Postgres trigger: the trigger function, and the trigger. Note the function must be defined prior to the trigger which calls it.

-- create a trigger function: That is a function returning trigger.
create or replace function tokens_bur_func()
returns trigger
language plpgsql
as $$
begin 
    if new.id = 1
    then
        update tokens
           set amount = new.amount
         where id = 2;
    end if;
    return new;
end ;
$$;

-- create the trigger
create trigger tokens_bur
    before update of amount 
    on tokens
    for each row execute procedure tokens_bur_func();

--- test 
select *
  from tokens
 order by id; 

-- do an initial update
update tokens
   set amount = 200
 where id = 1;
-- Query returned successfully: one row affected, 31 msec execution time. 
-- 1 row? Yes:  DML count does not see change made from within trigger.
-- but
select *
  from tokens
 order by id;

Hard coding ids in a trigger however is not very functional after all "update ... where id in (1,2)" would be much easier, and safer as it does not require the recursion stop logic. So a slightly more generic trigger function is:

-- More general but still vastly limited:
-- trigger that mirrors subsequent row whenever an odd id is updated.
create or replace function tokens_bur_func()
returns trigger
language plpgsql
as $$
begin 
    if mod(new.id, 2)=1
    then 
        update tokens
           set amount = new.amount
         where id = new.id+1;
    end if;
    return new;
end ;
$$; 

-- test
update tokens
   set amount = 900
 where id = 3; 

update tokens
   set amount = 18.95
 where id in (2,5);

select *
  from tokens
 order by id;

No matter how you proceed you required prior knowledge of update specifics. For example you said "might be id 2 I can set mirror from id 3" to do so you would need to alter the database in some manner either changing the trigger function or the trigger to pass parameters. (Triggers can pass parameters but they are static, supplied at create trigger time).
Finally make sure you got your recursion stop logic down cold. Because if not:

-- The Danger: What happens WITHOUT the 'stopper condition'
-- Using an almost direct conversion of your UDT
-- using new_id as  
create or replace function tokens_bur_func()
returns trigger
language plpgsql
as $$
begin 
    update tokens
       set amount = new.amount
     where id = new.id+1;
    return new;
end ;
$$;  

-- test
update tokens
   set amount = 137.92
 where id = 1; 
-- Query returned successfully: one row affected, 31 msec execution time. 
-- but 
select *
  from tokens
 order by id;

Collectives™ on Stack Overflow

Create Trigger For Update another row automatically on same table using postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related