1

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.

5
  • It doesn't seem scalable or easily maintainable, but should be alright. Will you be calling 25 different functions for each of the selection of row_id values? (note that there's no execute procedure in PostgreSQL though!) If not, probably PostgreSQL would perform better with a single normal trigger that does the checks in its body and calls the appropriate functions. Apr 21, 2016 at 6:46
  • Can't you just set a flag on the rows and have the trigger check for the flag and only process/send notification when the flag is set? Apr 21, 2016 at 7:41
  • @ZiggyCrueltyfreeZeitgeister: I will be calling only one function but it will lead to creation of 25 triggers one per row Apr 21, 2016 at 8:29
  • @JamesAnderson: Flag is not set at time of creation of SELLS resource (row). When SUBSCRIPTION resource (row) is created, then notify needs to be sent on CRUD changes to subscribed-to resource (in this case SELLS). Please check edit to the question for clear explanation Apr 21, 2016 at 8:33
  • I have a similar scenario, and chose to add a column with the name of the function that requires being executed, and the trigger verifies that the function exists and if it does it dynamically runs it, passing NEW as a parameter to it. Apr 21, 2016 at 9:13

1 Answer 1

2

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.

3
  • I was thinking of this but I am not sure of it's performance. So let's say there are 1,00,000 so every event on any of the row will lead to checking whether same id is there in SUBSCRIPTION, if yes then NOTIFY else do nothing. Performance wise how is it? Apr 21, 2016 at 8:45
  • P.S: I have 30 such tables like SELLS on which I want to trigger notification. So I will be creating 30 triggers, one per table and any event on any row of tables will lead to checking of whether present in SUBSCRIPTION or not. So will it scale gracefully ? Apr 21, 2016 at 8:47
  • Testing is the only way you can be sure of the performance comparison. Dynamically creating/maintaining 10s of triggers for 30 tables sounds like a nightmare. Apr 21, 2016 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.