User defined functions and triggers in DB?

User defined functions and triggers in DB?

by Robert Hawkey -
Number of replies: 7

I am having trouble finding in the documentation for the XMLDB editor and random googling how one goes about defining functions in the database for plugins.  I also want to create a trigger.

Can anyone point to anything I can look at to see how that's done?

Thanks!

Average of ratings: -
In reply to Robert Hawkey

Re: User defined functions and triggers in DB?

by Dorel Manolescu -
Picture of Plugin developers

Hi

Before trying to use DB triggers you may want to have a look at Events/Observer API:

https://docs.moodle.org/dev/Event_2

Regards

Average of ratings: -
In reply to Dorel Manolescu

Re: User defined functions and triggers in DB?

by Robert Hawkey -

Thanks, I did look at that.  I still want to use triggers though.

Average of ratings: -
In reply to Robert Hawkey

Re: User defined functions and triggers in DB?

by Davo Smith -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Peer reviewers Picture of Plugin developers

The XMLDB editor does not support triggers. As far as I'm aware, there is no way to make these work reliably across all the supported database back-ends.

You are, of course, welcome to manually create them on your own in-house database, as long as this is not something you are planning on publishing anywhere.

Average of ratings: -
In reply to Davo Smith

Re: User defined functions and triggers in DB?

by Robert Hawkey -

What about functions / stored procedures?

Average of ratings: -
In reply to Robert Hawkey

Re: User defined functions and triggers in DB?

by Marcus Green -
Picture of Core developers Picture of Particularly helpful Moodlers Picture of Plugin developers Picture of Testers

Davo's comment  would apply to any executable code embedded in the database. 

Average of ratings: -
In reply to Robert Hawkey

Re: User defined functions and triggers in DB?

by Luis de Vasconcelos -

If it's not something you want to redistribute (eg via a plugin you're building) then adding your own functions / stored procedures is ok. As long as you don't change the Moodle tables structure.

I have several stored procedures that push Moodle student data to QlikView dashboards and XML views that BizTalk exposes to a business consumer system.

The important thing is: READ ONLY. Don't allow any custom functions etc. to WRITE/UPDATE/DELETE data in the Moodle database. CRUD events should be done via the Moodle API's, not directly in the database. Else you WILL break things!

And be sure to check indexes and execution plans, etc. before you change anything in your Moodle database.

Average of ratings: -