How to synchronize database in INSERT INTO query

Question

Here is my query.

INSERT INTO compare_moodle 

            (userid, 

             username, 

             firstname, 

             lastname, 

             courseid, 

             coursename) 

SELECT mdl_user.id, 

       mdl_user.username, 

       mdl_user.firstname, 

       mdl_user.lastname, 

       mdl_course.id, 

       mdl_course.fullname 

FROM   mdl_user 

       JOIN mdl_role_assignments ra 

         ON mdl_user.id = ra.userid 

       JOIN mdl_role r 

         ON ra.roleid = r.id 

            AND r.id = 5 

       JOIN mdl_context c 

         ON ra.contextid = c.id 

       JOIN mdl_course 

         ON mdl_course.id = c.instanceid

When

mdl_user

or another database change (deleted / updated / inserted) how

compare_moodle

can be synchronized? I think use trigger but don't know how.

Answer 1

It's possible to use a VIEW in place of a table for the purpose you present. You'd do that like this:

CREATE OR REPLACE VIEW compare_moodle AS
 SELECT mdl_user.id AS userid, 
        mdl_user.username, 
        mdl_user.firstname, 
        mdl_user.lastname, 
        mdl_course.id AS courseid, 
        mdl_course.fullname AS coursename 
   FROM mdl_user 
   JOIN mdl_role_assignments ra    ON mdl_user.id = ra.userid 
   JOIN mdl_role r                 ON ra.roleid = r.id 
                                  AND r.id = 5 
   JOIN mdl_context c              ON ra.contextid = c.id 
   JOIN mdl_course                 ON mdl_course.id = c.instanceid;

This gives you a virtual table that is always transactionally up to date with your source tables. SQL best practice teaches us that this approach, without data redundancy, is the best way to do what you want.

The problem, of course, is performance. If you use this compare_moodle view a lot you may have slowdowns. Correct indexing of your tables can practically eliminate those slowdowns. I suggest you get your application working with a view, and then figure out whether you have a performance problem.

What you asked for in your question is a so-called materialized view -- a view represented by an actual table. In some makes and models of DBMS you can give a command like CREATE OR REPLACE MATERIALIZED VIEW ... and the DBMS does all the work. But, MySQL doesn't offer that.

There are various MySQLish approaches to managing materialized views. See here for more information. http://dba.stackexchange.com/questions/86790/best-way-to-create-a-materialized-view-in-mysql But this approach is a big hassle, and a maintenance headache compared to using an ordinary view.

How to synchronize database in INSERT INTO query

Sign up