Trigger in Doctrine Migrations

Question

I am trying to create a rather complex trigger in a doctrine migrations class:

https://github.com/2ndQuadrant/audit-trigger/blob/master/audit.sql

The first impulse was just to put the whole trigger code in one big blob and add it

with:

public function up(Schema $schema)
{
    $this->addSql($triggerSqlInABigBlob);
}

However the migration fails

SQLSTATE[42601]: Syntax error: 7 ERROR:  cannot insert multiple commands into a prepared statement

Is this even possible to manage in a doctrine migration? Is there a workaround / best practice to do this?

Answer 1

addSql in Doctrine's AbstractMigration expects one SQL command or array of multiple SQL commands. What you send is a string containig multiple SQL commands, which is not allowed. You can try this:

public function up(Schema $schema)
{
    $this->addSql(explode(';',$triggerSqlInABigBlob));
}

That should convert the string into an array where each element is one SQL command. The comments might a problem though.