CODEWITHSUNDEEP
mysqltriggersphpmyadmin
Ricardo García
Ricardo García

Reputation: 25

Trigger phpMyAdmin using variables

I am trying to create a trigger in phpMyAdmin, where I can modify the value of a field whenever it is inserted depending of a field name. I am using this syntax

CREATE TRIGGER lawyers AFTER INSERT ON wp_posts
FOR EACH ROW
BEGIN
UPDATE wp_posts SET post_content = '<!--:en-->&nbsp;<!--:--><!--:ES-->&nbsp;<!--:-->'      
WHERE ID = NEW.ID AND post_type = 'lawyers';
END

but I can't make it work, it says:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

Can you please help me?

Upvotes: 1

Views: 2060

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

You can't write such a trigger anyway, updating the same table you're inserting into. When I tested your trigger, I got this:

ERROR 1442 (HY000): Can't update table 'wp_posts' in stored function/trigger
because it is already used by statement which invoked this stored 
function/trigger.

The following should do the same thing as your trigger:

CREATE TRIGGER lawyers BEFORE INSERT ON wp_posts
FOR EACH ROW
BEGIN
 IF (NEW.post_type = 'lawyers') THEN
  SET NEW.post_content = '<!--:en-->&nbsp;<!--:--><!--:ES-->&nbsp;<!--:-->';
 END IF;
END

Answer: phpMyAdmin requires a special mode if you use CREATE TRIGGER or CREATE PROCEDURE.

  • Below the SQL Query box, there's a small form field labeled Delimiter. Set this to $$.

  • Enter your CREATE TRIGGER statement and terminate it with $$ after the last END.

  • Click the Go button.

See http://wiki.phpmyadmin.net/pma/Trigger

This is analogous to using the DELIMITER built-in command in the mysql command-line client.

Upvotes: 2

Related Questions