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.

  • 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. – Ziggy Crueltyfree Zeitgeister Apr 21 '16 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? – James Anderson Apr 21 '16 at 7:41
  • @ZiggyCrueltyfreeZeitgeister: I will be calling only one function but it will lead to creation of 25 triggers one per row – Siddharth Trikha Apr 21 '16 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 – Siddharth Trikha Apr 21 '16 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. – Ziggy Crueltyfree Zeitgeister Apr 21 '16 at 9:13
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.

|improve this answer|||||
  • 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? – Siddharth Trikha Apr 21 '16 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 ? – Siddharth Trikha Apr 21 '16 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. – James Anderson Apr 21 '16 at 8:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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