The day I created a SQL trigger and why it was a smart move

We have conflicting needs in one of our moodle instances.  We need to

• Remove completed or dropped enrollments from our moodle site/courses. 
• We also need to keep records or all enrollments and the work they did for perpetuity.

I created a new moodle instance and copied the externally configured DB responsible for enrollments.  I called it perpetuity_dbname.  Clever right?  Self describing?  Should persist?

Now my configuration looks like this:

• live moodle site consumes enrollment DB
• copy of live moodle site consumes copy of enrollments DB

What I was looking for was some sql code to copy records from the enrollment DB to the copy of enrollment DB.  Simple.  'ish'.  Clean?  Clean = logical, low maintenance, near the DBs in question.

First - I needed to find the SQL that would do it.  since this is a simple table to table copy operation, the SQL was not too hard to *find.

 INSERT INTO copyofliveDB.enrollments (courseid, username)
             SELECT courseid, username
             FROM liveDB.enrollments
                 WHERE (courseid,username) NOT IN
                  (
                SELECT courseid, username
                FROM copyofliveDB.enrollments
            );

This code is a little confusing for my brain...but it's solid.  Next step?  How to save it so I could call it.  I tested the SQL by adding records to the live DB table and watched them appear in the perpetuity DB.  I initially saved it as a snippet, but then moved it to a Stored procedure.  So I could call it from somewhere.

This is how I created a stored procedure.

DELIMITER //

CREATE PROCEDURE `copy_perpetuity` ()
LANGUAGE SQL
DETERMINISTIC
SQL SECURITY DEFINER
COMMENT 'copies records in the live DB that do not exist in the copyofliveDB'
BEGIN
    INSERT INTO copyofliveDB.enrollments (courseid, username)
             SELECT courseid, username
             FROM liveDB.enrollments
                 WHERE (courseid,username) NOT IN
                  (
                SELECT courseid, username
                FROM copyofliveDB.enrollments
            );
END//

Now that I had the SQL and had saved it to a stored procedure called copy_perpetuity - the last hurdle was how to call it.  My first thought was to call the file from windows on the cron script.  This makes some sense because the cron script is timed - so this stored procedure would be called automatically, a trigger of sorts.  A trigger to copy the new records from the live DB to the copy.

I looked at something like this:

Then I saw this post/code snippet on creating a mysql event - and specify the frequency of the call to the stored procedure.

CREATE EVENT myevent
    ON SCHEDULE EVERY 1 Day
    DO
      copy_perpetuity();

But, I ended up creating a trigger on the insertion event of the live DB table.  This seemed clean and simple.  This is the trigger created.

CREATE TRIGGER copy_perpetuity AFTER INSERT ON enrollments FOR EACH ROW CALL copy_perpetuity();

I used the other TRIGGER commands will figuring this out.  Straightforward.

DROP TRIGGER copy_perpetuity;
SHOW TRIGGERS;

 I tested the trigger by manually adding a record to the liveDB table and immediatly browsing the copyDB table and seeing the record.  Ya.

Moral of the story?

Do not rush to judgement.  I rolled around in this task for the better part of the day yesterday, read some things, thought about, marinated in the concept.  This am, I can in and in about 2 hrs had implemented and tested the solution.

Other links that helped.

Triggers
More triggers
Stack overflow post