MySQL trigger to update wordpress post_password in wp_posts

In working on a special project as of late, I ran into a situation where I needed to have the post password reset on posts upon an insert into a custom mysql table in the same database where wordpress was housed.  Each post in this environment acts like a “checkin” and “checkout” type setup where users are checked out resources which relate to each post and then they check these back in.  With that being said, each user was assigned a password via the post_password function inside wordpress.  We wanted the ability of this post password being set back to our default master post password so the administrator didn’t have to do this each time or have to remember to do this each time.

The solution was a MySQL trigger to update wordpress post_password in wp_posts table.  When our custom code “checked” the post back in, the trigger updates the equivalent post password via the trigger.  Also, there may be a better way to do this or even a much more streamlined version of the code that could be used, however, in a crunch this is what I came up with that works.

The code:

create trigger passchange AFTER INSERT ON system.checkin

FOR EACH ROW

BEGIN

DECLARE namevar varchar(20); 
DECLARE inoutvar varchar(20);

select system_number INTO namevar

from 
(select 
    *
from
    (select 
        system_number,
            firstname,
            lastname,
            instatus,
            outdate,
            indate,
            date_entered,
            activity
    from
        checkin) AS checkinselect
HAVING date_entered = (select 
        MAX(date_entered) from checkin)) AS checkinsel;

select checkinstatus INTO inoutvar

from 
(select 
    *
from
    (select 
        system_number,
            firstname,
            lastname,
            instatus,
            outdate,
            indate,
            date_entered,
            activity
    from
        checkin) AS checkinselect
HAVING date_entered = (select 
        MAX(date_entered) from checkin)) AS checkinsel;

IF inoutvar='In'

THEN
update wp_posts t2
INNER JOIN system.checkin t1 ON (t1.system_number=t2.post_title)
SET t2.post_password = 'masterpass'
WHERE t2.post_title=namevar
AND t2.post_type='post';

END IF;

END$$
DELIMITER ;

The above trigger is not too difficult to understand as it contains pretty straightforward MySQL syntax.  However, just a brief overview – there are a couple of subselects that help to harvest the latest inserted data into our table as we want to make sure we are changing the record that is being changed in our latest checkin.  Then aside from the select statements to gather the right post which we are checking in, there is an IF statement that basically makes sure that the status is an ‘In’ post as well as a JOIN to the wp_posts table to update the post_password to our masterpass where the post_title equals the system_number (post) in our checkin system.