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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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

Method 2

run:

delimiter $$

before running the CREATE

Method 3

The UPDATE query you’re running inside the trigger is an update query like any other, except that it’s running within the trigger. Right now you’re updating ALL records and incrementing the points field. If you want to limit it to only a particular record(s) that are related to whatever caused the trigger to fire, you’ll have to add that to the UPDATE query’s WHERE clause.

MySQL has on way of knowing what your intent with the UPDATE query is, just as it has no idea what you really mean when doing a normal insert/update/delete – it’s up to you specify exactly what must be done.

In the case of an INSERT trigger, there usually isn’t an “old” record to refer to, as you’re creating something brand new (unless it’s an insert … on duplicate key update situation).

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating