Problem with triggers in database

Problem with triggers in database

by Vladimir Miranovic -
Number of replies: 3

Hello,

I have a problem in my Organization, we have a Moodle (2.9 and 3.2) with online courses and we need to remove completed or dropped enrollments from our moodle site/courses, but also we need to keep records of all enrollments, completions and group membership, for reporting (quarterly, yearly or ad-hoc).

For that reason I got idea to create copy of Moodle original tables in the same database (live Moodle). For example for enrol (original table) I made xenrol (copy of original table, with less fields), and for course (original table) I made xcourse (copy of original table, with less fields). I also made user_enrolments, groups, groups_members, course_completions, and for user I don’t need a copy, as also for grades (grades_grades_history), and certificates (there is a plugin for keeping cetificates).

After that I created a trigger on the after insert event on original course table (and similar on other tables).

DELIMITER $$

CREATE TRIGGER writetoxcourse

AFTER INSERT

ON m320_course FOR EACH ROW

BEGIN

                INSERT INTO m320_xcourse (xid, xcategory, xfullname, xshortname, xidnumber, xstartdate, xenddate, xgroupmode, xgroupmodeforce, xtimecreated, xtimemodified)

                VALUES (id, category, fullname, shortname , idnumber, startdate, enddate, groupmode, groupmodeforce, timecreated, timemodified);

END$$

DELIMITER ;

 

However, when I try to create new course, I get error from Moodle (same with other tables), and when I remove trigger from course table everything is functioning normally.

Idea of make copies of the tables in same database coming from simplicity of accessing and doing reports from standard “Configurable Reports” or “Ad-hoc”.

Maybe someone will write local plugin with this “Mini SYS in Moodle” in the future, but we need solution now (our funding depends on reporting), please help.

 
Average of ratings: -
In reply to Vladimir Miranovic

Re: Problem with triggers in database

by Darko Miletić -

Does your database user used with Moodle have TRIGGER prviliege enabled?

https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html#priv_trigger

You did not provide any specific error and that is required if you want more help.

Average of ratings: -
In reply to Darko Miletić

Re: Problem with triggers in database

by Vladimir Miranovic -

Hello Darko,

about your post: "The TRIGGER privilege enables trigger operations. You must have this privilege for a table to create, drop, execute, or display triggers for that table".

I created trigger in database, I can drop the same trigger, but I don't know for execute privilege. I am just trying to create for example new course thru Moodle interface and then Moodle show an error:

https://docs.moodle.org/32/en/error/moodle/dmlwriteexception

and when I enable Debug showing next:

Debug info: Champ 'id' inconnu dans field list
INSERT INTO m320_course (fullname,shortname,category,visible,startdate,enddate,idnumber,format,lang,newsitems,showgrades,showreports,maxbytes,enablecompletion,groupmode,groupmodeforce,defaultgroupingid,timecreated,timemodified,sortorder,summary,visibleold) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
[array (
0 => 'NMNEw',
1 => 'New 2',
2 => '1',
3 => '1',
4 => 1499310000,
5 => 1530846000,
6 => '',
7 => 'topics',
8 => '',
9 => '5',
10 => '1',
11 => '0',
12 => '0',
13 => '0',
14 => '0',
15 => '0',
16 => '0',
17 => 1499256187,
18 => 1499256187,
19 => 0,
20 => '',
21 => '1',
)]


Error code: dmlwriteexception
Stack trace:

    line 477 of \lib\dml\moodle_database.php: dml_write_exception thrown
    line 1172 of \lib\dml\mysqli_native_moodle_database.php: call to moodle_database->query_end()
    line 1218 of \lib\dml\mysqli_native_moodle_database.php: call to mysqli_native_moodle_database->insert_record_raw()
    line 2307 of \course\lib.php: call to mysqli_native_moodle_database->insert_record()
    line 157 of \course\edit.php: call to create_course()

I don't know have to resolve that.

Idea is that somebody write plugin with this MiniSYS inside Moodle, for all moodle users that they don't need real (expensive) outside SYS for tracking all information that Moodle removing from database once the user is unenroled from course.

I just need to confirm that idea is working, in my local test Moodle 3.2, when I wrote Live Moodle I was thinking on live database (inside of Moodle database).

Thank you for your interest, and trying to help,

Vladimir


Average of ratings: -
In reply to Vladimir Miranovic

Re: Problem with triggers in database

by Vladimir Miranovic -

Got it!

Error is in the trigger, should be like this:

DELIMITER
CREATE TRIGGER writetoxcourse
AFTER INSERT
ON m320_course FOR EACH ROW

BEGIN
    INSERT INTO m320_xcourse (xid, xcategory, xfullname, xshortname, xidnumber, xstartdate, xenddate, xgroupmode, xgroupmodeforce, xtimecreated, xtimemodified)
    VALUES (New.id, New.category, New.fullname, New.shortname , New.idnumber, New.startdate, New.enddate, New.groupmode, New.groupmodeforce, New.timecreated , New.timemodified);
END
DELIMITER ;

with the "New." in front of VALUES items (New.id, New.cate......

Thanks,

Vladimir

Average of ratings: -