General developer forum

Cannot write to database error when setting up trigger on mdl_user

 
Mt. Fuji
Cannot write to database error when setting up trigger on mdl_user
 

Hello,

I'm not sure if this is the correct place to ask this question but....

I've been wracking my brain all day today trying to setup a trigger on the mdl_user table that writes to the mdl_cohort_members table in order to automatically join users who create accounts via email based authentication but I keep getting the 

error writing to database 

message from Moodle when I try to create an account, to test the setup, using email based account creation.

I'm fairly sure my sql syntax is correct (no errors being generated within mysql). I have a similar trigger setup on a different table in the db and it works fine...I'm starting to wonder if there is something in the Moodle code that isn't wanting to cooperate with me...?

Any advice anyone can provide will be greatly appreciated

Regards

Jason

Below is the trigger I have tried (one form of it at least...I've tried various styles)

BEGIN

IF mcvdb.mdl_user.auth = ‘email’ THEN

INSERT INTO mcvdb.mdl_cohort_members (cohortid, userid)

SELECT ‘138’, id as ‘userid’

FROM mcvdb.mdl_user

ELSEIF mdl_user.auth = ‘ldap’ THEN

INSERT INTO mcvdb.mdl_cohort_members (cohortid, userid)

SELECT ‘1’, id as ‘userid’

FROM mcvdb.mdl_user

END IF;

END

Also, I am creating the trigger through Adminer...the delimiter appears to be adjusted initially so I think the use of the IF ELSEIF conditions are ok...?


 
Average of ratings: -
Davo
Re: Cannot write to database error when setting up trigger on mdl_user
Core developersParticularly helpful MoodlersPlugin developers

That seems like a very bad way to go about handling the situation, when Moodle already has built-in support for doing such things.

Create a new local plugin, with an event observer that triggers off '\core\event\user_created', then get that to check the auth type and call cohort_add_member(). This has many advantages (it will work if you deploy the code to a dev server, not just on your live site, it properly triggers the 'cohort_member_added' event, that other plugins might use, it correctly sets the 'timeadded' field and is much easier to maintain).

https://docs.moodle.org/dev/Event_2 has details about setting up event observers.


 
Average of ratings:Useful (1)
Mt. Fuji
Re: Cannot write to database error when setting up trigger on mdl_user
 

Davo,

I don't question your appraisal of my method being far from the best...but, given my resources (and very modest programming skills) it's the most efficient workaround for now. 

I just can't figure out why I'm getting the database write error in this case but have no issues with the one other trigger I've set up in the db. I'm guessing it has something to do with the trigger interfering with the db write but it is an after insert trigger so that doesn't make sense to me....

Thanks for your point in the ideal direction though. I'm on sabbatical now and do have time to improve my skills so maybe I'll go that route for a more permanent fix but for now I really need this to work on the main live site I maintain.

Regards

Jason

 
Average of ratings: -
Picture of Nitesh Kumar
Re: Cannot write to database error when setting up trigger on mdl_user
 
Hi Jason,


I think there is the issue with 

INSERT INTO mcvdb.mdl_cohort_members (cohortid, userid)

it should be 

INSERT INTO mcvdb.mdl_cohort_members (cohortid, userid) values (cid, userid)

Regards,

Nitesh

 
Average of ratings: -
Mt. Fuji
Re: Cannot write to database error when setting up trigger on mdl_user
 

Nitesh,

Thanks for the suggestion. Unfortunately, that doesn't work. I've tried using the values directly...but I think they need to be in the format ('138', id) because:

1. I'm using the hard-coded value of specific cohorts ('138' and '1' in this case)

2. The id must come from the mdl_user table (id of the user)

For some reason, even if the trigger syntax is seemingly correct, I'm getting the 'write to database' error...I suppose because the trigger is not resolving correctly and thus causing a problem with the database write...but, that doesn't make sense to me since the trigger is an 'after insert' trigger.

I've been toying with modifying one of the local tools related with cohort enrollment (either profilecohort or chortrole) but am coming up short in terms of programming knowledge...especially OOP. sad

Regards

Jason

 
Average of ratings: -
Picture of Dan Marsden
Re: Cannot write to database error when setting up trigger on mdl_user
Core developersMoodle Course Creator Certificate holdersParticularly helpful MoodlersPlugin developersPlugins guardiansTestersTranslators

why don't you use one of the cohort plugins to do this instead?

this one lets you look at the "auth" field:

https://moodle.org/plugins/auth_mcae

or this one might do it too:

https://moodle.org/plugins/local_profilecohort

 
Average of ratings: -