How to add triggers (MySQL) using Phinx?
Nobody has claimed this yet.
- 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
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- 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