CODEWITHSUNDEEP
sqlpostgresqltriggerssql-update
vtoribio
vtoribio

Reputation: 3

How can I solve max_stack_depth?

I'm super new with PGADMIN and I have a problem. I have to create a trigger that updates the password to '123456' and the reset mark to 'Y' every time a change is detected in the mail or in the username of a table in my database. My code is:

CREATE OR REPLACE FUNCTION password_reset()
RETURNS trigger AS $$
BEGIN 
    UPDATE web_content.tb_user SET user_password = '123456' 
    WHERE user_email = NEW.user_email OR user_name = NEW.user_name;
    UPDATE web_content.tb_user SET password_reset = 'Y'
    WHERE user_email = NEW.user_email OR user_name = NEW.user_name;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER TR_update BEFORE update on web_content.tb_user
for each row
execute procedure password_reset();

When I try to make an UPDATE on my table the error error max_stack_depth appears. I don't know how to solve it. Can anyone help me, please?

Thanks in advance

Upvotes: 0

Views: 1006

Answers (1)

GMB
GMB

Reputation: 222542

Don't update the table in the trigger function. This generates an infinite loop where the update statement of the function fires the trigger again, and eventually raise the error you are getting.

Instead, you can set the new value directly in the function, before the new row is updated in the table. If you want to fire the trigger only when some specific columns are modified, you can use a when clause in the trigger definition.

create or replace function password_reset()
returns trigger as $$
begin 
    new.user_password = '123456';
    new.password_reset = 'Y';
    return new;
end
$$ language plpgsql;

create trigger tr_update before update on web_content.tb_user
for each row
when (old.email is distinct from new.email or old.username is distinct from new.username)
execute procedure password_reset();

Upvotes: 3

Related Questions