PostgreSQL trigger based on function return value

Question

I'm new to triggers. I have a function that I use to check if a password is valid that returns a boolean value. Is it possible to trigger a trigger only if the value is true? (This would update the last_login_time based on successful password checking).

PS I'm using PostgreSQL 9.0.4

Is it possible to write such trigger?

What sort of process are you using to log people in? Triggers are triggered by inserts, updates, or deletes on a table but I'm guessing that you're not doing any of those. — mu is too short
– mu is too short, Commented Dec 12, 2011 at 9:20

soulcheck · Accepted Answer · 2011-12-12 09:47:37Z

1

No, but you can check the password again in your trigger and execute the logic needed if it validates. Triggers can fire only after or before INSERT, UPDATE, or DELETE, and either for each row or for the whole statement. There's no way to conditionally fire a trigger of certain type AFAIK.

Edit: There is a WHEN clause in CREATE TRIGGER statement that allows one to specify conditions when the trigger should fire, but then you'd have to check the validation in the condition which is equivalent to what i posted above, only covered with syntactic sugar.

Still it's probably something that should be done in stored procedure or outside of DB.

edited Dec 12, 2011 at 9:47

answered Dec 12, 2011 at 9:22

soulcheck

36.9k6 gold badges95 silver badges91 bronze badges

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

4 Comments

mu is too short Over a year ago

There is WHEN as of version 9 for adjusting the conditions that will trigger a trigger.

soulcheck Over a year ago

the problem with WHEN is that OP would have to call the validation function to compute the condition, which is equivalent to putting an if in the beginning of the trigger function. I'll update the answer.

soulcheck Over a year ago

@muistooshort but then maybe that's what he's looking for. updated.

mu is too short Over a year ago

Hence my comment on the question, this doesn't sound like a job for a trigger but maybe we'll get some clarification.

Collectives™ on Stack Overflow

PostgreSQL trigger based on function return value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related