0

I am trying to apply a trigger to an existing table and column through PHP. When executing the PHP code, no error is thrown, but the trigger is not created.

My PHP code is:

//file: migrations.php

// created_at column already defined in a previous migration
//...

$migrations[3]['message'] = "added created_at, updated_at columns on rate table";
$migrations[3]['created'] = "2015-09-05 08:28:00";
$migrations[3]['sql']     = "
DELIMITER |
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END |
    DELIMITER ;
";

// apply migration
foreach ($migrations as $key => $value) {
    // run the migration SQL for the current iteration
    $sth = $dbh->prepare( $value['sql'] );
    $sth->execute();
}

heres what does work

  • Copying and pasting the trigger creating SQL into Phpmyadmin
  • getting rid of the DELIMITER and BEGIN END statements e.g. if I reduce the SQL to 
$migrations[3]['sql']     = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
        SET NEW.created_at = CURRENT_TIMESTAMP;
";

It does run from PHP, problem is I dont believe I can run multiline statements without DELIMITER and BEGIN END.
I have tried

  • escaping the delimiter character
  • using different delimiter characters

What can I do to get the PHP to run the SQL with the DELIMITER and BEGIN END statements?

the_velour_fog
  • 2,094
  • 4
  • 17
  • 29

1 Answers1

2

yes, you will have to get rid of those DELIMITER, just have the below code

$migrations[3]['sql'] = "
CREATE TRIGGER rate_created_at BEFORE INSERT ON rate
FOR EACH ROW
    BEGIN
        SET NEW.created_at = CURRENT_TIMESTAMP;
    END;";

See this another post saying same thing PHP: multiple SQL queries in one mysql_query statement

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • thanks Rahul, that works! I wonder why MySQL doesnt think the whole trigger statement is not finished when not using a different delimiter character? – the_velour_fog Sep 04 '15 at 21:56
  • `DELIMITER` isn't part of SQL syntax, it's a feature of the interactive user interface. – Barmar Sep 04 '15 at 22:18
  • This is why you can't use it at sqlfiddle, you have to use their menu to set the delimiter. – Barmar Sep 04 '15 at 22:18