Delete command not work after create trigger

Question

I create a simple function and trigger to delete all rows from tb_users when a row from tb_city is deleted:

create or replace function delete_user()
returns trigger as
$$
begin
    delete from rl_user_city where user_id in (select distinct user_id from rl_user_city where city_id = old.id) and city_id = old.id;
    return new;
end;
$$
language plpgsql;

drop trigger if exists t_delete_user on tb_city;

create trigger t_delete_user
before delete on tb_city
for each row
execute procedure delete_user();

After I create this trigger, the command delete is not working: delete from tb_city where id = 385;

My tables:

tb_city:

id: int
city: string

tb_user_city:

user_id: int
city_id: int

I run, but the row is not deleted...

Any idea?

Why don't you use a foreign key for this? You're now trying to reinvent the wheel... — Frank Heikens
– Frank Heikens, Commented Jul 27, 2022 at 3:44

Patrick · Accepted Answer · 2022-07-27 03:50:32Z

2

In a before delete trigger the value NEW is null. A null return value will block the deletion from happening. You need to RETURN OLD from a delete trigger instead.

If I am not mistaken, if you simply want to delete all users that are registered in a city you are deleting the DELETE command in the trigger function can be much simpler:

DELETE FROM rl_user_city WHERE city_id = OLD.id

answered Jul 27, 2022 at 3:50

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Delete command not work after create trigger

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