Postgresql – Trigger for a particular row

plpgsqlpostgresqltrigger

I am trying to dynamically create trigger for a particular row instead of for each row. So if a table has 1000 rows and I want notification only on 25 rows, I am creating triggers like:

CREATE TRIGGER Trig
AFTER UPDATE ON Sells
FOR EACH ROW
WHEN (row_id="something")
    EXECUTE PROCEDURE sendNotif();

So with this only when my particular row is edited only then my sendNotif() is called.

Is this a good approach ?? Is there a benefit when compared to trigger on the whole table ??

I am doing this because I want to send notifications only for rows which have a subscription.

EDIT: Scenario explained:

So I have two tables SELLS and SUBSCRIPTION. When a user wants to subscribe to sells resource (row), it creates a subscription resource (row). Once subscription resource is created, notification needs to sent whenever CRUD operations are performed to the subscribed-to (Sells) resource. So subscription resource is created once and after that notification is sent based on events (CRUD) on Sells resource. That is why trigger was on Sells resource to track it's changes.

Best Answer

Dynamically creating multiple triggers for individual rows sounds quite nasty. This is not likely to scale well.

After reading your update I would suggest you implement 1 trigger that fires for all CRUD operations on the SELLS table. This trigger checks to see if the modified rows of SELLS are in the SUBSCRIPTION table. If they are send a notification, if not don't send notification.

Related Question