cakephp / cakephp/phinx

How to add triggers (MySQL) using Phinx?

Open
#1,418 2 comments 6 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

enhancement
Dominant language
PHP
Stars
4.5k
Forks
884
PR merge metrics
No merged PRs in 30d

Description

For some reason the SQL queries in this migration run just fine if I execute them in HeidiSQL. The triggers are created.

It just doesn't work for some reason when I try to put them into a migration. I've already figured out that there seems to be a problem with PDO as mentioned here PDO support for multiple queries (PDO_MYSQL, PDO_MYSQLND). I've tried to replicate the example from this question but without success.

So what is still wrong with this? Or is there any "native" way that Phinx provides to create triggers?

<?php
use Cake\Datasource\ConnectionManager;
use Migrations\AbstractMigration;

/**
 * AddingConstraintsToTransactions
 */
class AddingConstraintsToTransactions extends AbstractMigration {

    /**
     * Add constraints to the transactions so that either credit or debit has
     * to be filled but at least one of must be filled at the same time.
     *
     * @link https://stackoverflow.com/questions/6346674/pdo-support-for-multiple-queries-pdo-mysql-pdo-mysqlnd
     */
    public function up() {
        $config = ConnectionManager::getConfig('default');
        $connection = 'mysql:host=' . $config['host'] . ';dbname=' . $config['database'];
        $db = new PDO($connection, $config['username'], $config['password']);
        $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

$sql = " 
DELIMITER //

CREATE TRIGGER `wa3`.`InsertCreditOrDebitNotNull` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) 
    THEN 
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) 
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- first trigger

DELIMITER ;";

        $db->exec($sql);

$sql = "
DELIMITER //
CREATE TRIGGER `wa3`.`UpdateCreditOrDebitNotNull` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) 
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) 
    THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- second trigger

DELIMITER ;";

        $db->exec($sql);
    }

    /**
     * @inheritDoc
     */
    public function down() {
        $this->query('DROP TRIGGER IF EXISTS `InsertCreditOrDebitNotNull`');
        $this->query('DROP TRIGGER IF EXISTS `UpdateCreditOrDebitNotNull`');
    }

}

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by reproducing the migration in the issue, focusing on the two PDO exec calls and the MySQL trigger statements. Check Phinx's migration query APIs and existing database-related tests or documentation for trigger support. Done means establishing whether trigger creation is supported and documenting or implementing a clear, tested path.

Written by the indexing model from the issue text.

Assessment

Tech stack
mysql, php
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.