CODEWITHSUNDEEP
sqlpostgresql
baartko
baartko

Reputation: 117

Update records in database automatically

Let me describe my scenario here.

I am having a table with multiple records, where the name is the same, as it's gonna be records for the same person updated on daily basis.

Right now, I am trying to find out the easiest way to update all the names accordingly.

  1. Name is going to be updated (TestName -> RealName)
  2. I want this change to be applied to all the records with the same name, in this case, "TestName"

I can do a single query, but I am trying to find if there's an automatic way to do this correctly.

![enter image description here

Been trying using a triggers, but in most cases, I am ending with an infinite loop, as I am trying to update the table, where a trigger is actually bound to, so it's invoking another update and so on.

I don't need an exact solution, just give me some ropes about how it can be achieved, please.

Upvotes: 0

Views: 98

Answers (2)

SinisaT90
SinisaT90

Reputation: 92

You can use event triggers in postgresql https://www.postgresql.org/docs/9.3/sql-createeventtrigger.html

Upvotes: 0

klin
klin

Reputation: 121754

The problem may be simply resolved by using the function pg_trigger_depth() in the trigger, e.g.:

create trigger before_update_on_my_table
before update on my_table
for each row 
when (pg_trigger_depth() = 0) -- this prevents recursion
execute procedure before_update_on_my_table();

However, it seems that the table is poorly designed. It should not contain names. Create a table with names (say user_name) and in the old table store a reference to the new one, e.g.:

create table user_name(id serial primary key, name text);

create table my_table(id serial primary key, user_id int references user_name(id));

Upvotes: 1

Related Questions