0

This is an extension of this question : SO

I want to implement the mysql trigger helpfully provided by @Shadow into a Wordpress plugin. The query=trigger is

$sql_trigger = "
drop trigger if exists bi_tbl_B $$
delimiter $$

create trigger bi_tbl_B before insert on tbl_B
for each row begin
   DECLARE v_slug as varchar(255);
   DECLARE v_url as text;
   SELECT url, slug INTO v_url, v_slug FROM tbl_A WHERE id = NEW.id;
   NEW.url=v_url;
   NEW.slug=v_slug;
end;
$$

delimiter ;
";

mysqli_multi_query($wpdb->dbh,$sql_trigger); 

I have few questions:

1) Is this query correct or I need to make changes?

2) Do I have to use any one of this

global $wpdb; or

$mysqli = new mysqli();
$mysqli->connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

3) Is the query safe?

0
  1. No idea
  2. yes, use $wpdb
  3. seems like it should be. OTOH if this is something you want to do in a plugin, I am not sure if normal wordpress users (as defined in the DB) have trigger creation privilege.

just be careful with what you are trying to do, as if you have object caching and the data you create is read by wordpress APIs, you might end up with a mismatch between the cache and the DB.

  • thnx a lot for the suggestions, one question: does that mean wordpres plugins cannot have triggers? If I understand correctly, At CREATE TRIGGER time, the user who issues the statement must have the TRIGGER privilege. Now, I am in a bind! thnx for pointing it out. – mesumosu Oct 3 '16 at 7:13
  • I don't know, and I will admit that I never saw this permission before or triggers being used at all in plugins. I just assume that on shared hosting DB users are configured with the least amount of permissions possible and trigger creation is not essential for a functioning wordprss. (and thx for making me investigate, I didn't know it was possible to create triggers without being a root user of the DB) – Mark Kaplun Oct 3 '16 at 7:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.