Wolfpack'08 Wolfpack'08 - 2 months ago 16
MySQL Question

MySQL: How do I use delimiters in triggers?

Someone told me that I need to use a delimiter in my trigger. I'm looking at the MySQL manual page, and I'm finding it in the example; however, it's not in the generic documentation.

Here's the code I am trying to correct:

CREATE TRIGGER adult BEFORE INSERT OR UPDATE ON table5.column5
FOR EACH ROW BEGIN
UPDATE table1 SET column5 = table5.column5 WHERE table5id = table1xtable5.table5id WHERE table1xtable5.table1id = OLD.table1.id$
END$


Honestly, I have no idea how to do this, and the documentation at http://dev.mysql.com/doc/refman/5.5/en/create-trigger.html seems like a horribly inadequate reference. For example, when reading about "
old
" and "
new
", it references the "
subject table
"--the table associated with the trigger. There are two tables associated with this trigger, and I'm actually trying to associate more tables with this trigger.... When looking at the example code, the
UPDATE
line, which effects table4, only columns within table4 are used (they're adding 1 to the value). Also, they're using
WHERE ~ a3 = NEW.a1
. I don't think that id's work that way. There shouldn't be a universal ID that works across tables. They should definitely be using an interdependency table in the example, for applicability.

I actually asked a similar question earlier today about what a good method for doing this would be, but now I'm wondering, simply, if there is a method. Thanks for reading. :)

Here is an example of a trigger that updates another table, but it doesn't seem to use a delimeter:

CREATE TRIGGER mytrigger BEFORE INSERT ON odp
FOR EACH ROW
UPDATE total_points SET points = points + NEW.points;


What isn't explained in this example is how
odp
and
total_points
overlap. How can we be sure that the
total_points
table's
points
row is the correct
points
row? Which row in the
points
table would MySQL update? x//

So, shouldn't there be some way to specify which of the total_points.points rows to update
BEFORE INSERT ON odp
?

Answer

Part 1

The delimiters are used for source objects like stored procedure/function, trigger or event. All these objects may have a body - code within BEGIN...END clause.

All statement in MySQL scripts should be ended with delimiter, the default is ';'. But what to do if source object has body with some statements, e,g:

INSERT INTO table1 VALUES(1);

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END;

INSERT INTO table1 VALUES(2);

How many statemants? 3 or 8? The answer is three, because script has two INSERTs and one CREATE PROCEDURE statements. As you see, CREATE PROCEDURE has some internal statements too; we should say to MySQL client that all these statement (inside BEGIN...END) - are part of ONE statement; we can do it with a help of delimiters:

INSERT INTO table1 VALUES(1);

DELIMITER $$

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END$$

DELIMITER ;

INSERT INTO table1 VALUES(2);

Note, when your trigger has no BEGIN...END clause, delimiters may be omitted.


Part 2

Without delimiters the statement will be parsed as -

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';

instead of -

CREATE PROCEDURE procedure1()
BEGIN
  SET @s = 'SELECT SQRT(POW(?,2) + POW(?,2)) AS hypotenuse';
  PREPARE stmt2 FROM @s;
  SET @a = 6;
  SET @b = 8;
  EXECUTE stmt2 USING @a, @b;
END
Comments