Elaborate more on how SQL statements should be crafted

[herbdool]

I've been struggling with getting the SQL working. I've dug into the code a bit but would be good for everyone to have a bit more information on how the queries work. For example, what "NEW" means, whether any table could be used as a trigger table, does it work on UPDATE or DELETE or only INSERT, etc.

All in all, a very helpful extension. Thanks for the useful extension!

[jmcclelland]

Hi - glad the extension is helpful. Here are some general references:

• mysql docs on triggers
• civicrm trigger info hook

Triggers apply to update, delete and insert. When writing a trigger the NEW key word indicates the record that is inserted or changed and OLD indicates the record that was deleted. A simplified example of a trigger would be if you had a contact table with a total_contributions field and a contributions table with a foreign key contact_id that included all the contributions.

Then, you could create trigger that would execute on INSERT or UPDATE for the contribution table that was:

UPDATE contact SET contact.total_contributions = (SELECT SUM(amount) FROM contribution WHERE contact_id = NEW.contact_id)

And a DELETE trigger that was:

UPDATE contact SET contact.total_contributions = (SELECT SUM(amount) FROM contribution WHERE contact_id = OLD.contact_id)

With these examples NEW.contact_id and OLD.contact_id would always be equal to the value of contribution.contact_id for the row that was inserted, updated or deleted.

With these triggers the total_contributions would always be accurate.

[herbdool]

Thanks @jmcclelland. This would be a valuable addition to the README. I have some follow-up questions.

• The extension handles the INSERT, UPDATE, DELETE, correct? The hook's SQL is just there to determine what to look for?
• It's still unclear if other tables can have triggers with this extension. What if I wanted a summary field to be updated based on civicrm_membership_log or civicrm_membership tables? (In case a membership is added without a payment related).
• What can we do if we change a hook's query? How do we get it to update? Do we uninstall the extension and reenable? Or run the cron job?

Thanks again.

[jmcclelland]

Hi Herb - The README file is intended for users of the extension, not developers who are interested in extending it. Perhaps you would like to contribute a HACKING.md file for developers?

And yes, the extension handles INSERT, UPDATE and DELETE. Each SQL statement in the custom file is designed to produce a single value that is then inserted into one of the summary fields.

With version 4, you can now run updates based on any table, provided there is a way to link it back to a contact id. If there is a contact id in the table you want to be the trigger, it's easy because you can reference NEW.contact_id to get the right reference for the summary field row. If there isn't a contact_id in your table, then you need to add a record to the $custom['tables'] array (civicrm_line_item is a good example of how to do that).

If you change a hook's query, you can simply go to the Summary Fields admin screen and resave. All data will be updated.

[herbdool]

Sure, here's a PR https://github.com/progressivetech/net.ourpowerbase.sumfields/issues/50

[jmcclelland]

Thank you!