Migrations -> create trigger sqlite3

Yii 2.0 General Discussions
1

Hi,
i’m struggling to use migrations to drop and create new trigger in one of the tables in sqlite3.
I’ve found some informations about that, but none of solutions works for me. It shows the same error if using $sql = <<createcommand() and same error in $sql = “SQL” and then just execute();

Here is the code:

 $this->execute('DROP TRIGGER IF EXISTS new_id_person');

        // Create new trigger
        $this->execute('
            CREATE TRIGGER new_id_person
            AFTER INSERT ON history
            FOR EACH ROW
            WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
            BEGIN
                UPDATE history
                SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
                    PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
                WHERE idHist = new.idHist;
            END;
        ');

OR this code

  $triggerSql = <<<SQL
CREATE TRIGGER new_id_person
AFTER INSERT ON history
FOR EACH ROW
WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
BEGIN
    UPDATE history
    SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
        PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
    WHERE idHist = new.idHist;
END
SQL;

        Yii::$app->db->createCommand($triggerSql)->query();

gives me the same error in output

*** applying m231030_113828_update_history_trigger_person
    > execute SQL: DROP TRIGGER IF EXISTS new_id_person ... done (time: 0.000s)
Exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 1 incomplete input
Failed to prepare SQL: CREATE TRIGGER new_id_person
AFTER INSERT ON history
FOR EACH ROW
WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
BEGIN
    UPDATE history
    SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
        PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
    WHERE idHist = new.idHist;'

in /Users/xxx/Dropbox/xxx-SITES/YII-RCP/YII2-APP/vendor/yiisoft/yii2/db/Command.php:272

Error Info:
Array
(
    [0] => HY000
    [1] => 1
    [2] => incomplete input
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 incomplete input'

Any ideas how to make it work ? :face_with_raised_eyebrow:

2

I guess you should remove the semicolon.

3

The problem is, that this code works when run in query console instead of migration.
I’ve just tried what you’ve proposed but it end with error like this below:

Error Info:
Array
(
    [0] => HY000
    [1] => 1
    [2] => near "END": syntax error
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 near "END": syntax error'
4

As above. You can also check if your actual prepared command looks like you expected:

Yii::$app->db->createCommand($triggerSql)->sql; //echo it
5

Hi,
after echo I get it as I see properly, when executed what echo outputs it works well in query console.

main> CREATE TRIGGER new_id_person
          AFTER INSERT ON history
          FOR EACH ROW
          WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
      BEGIN
          UPDATE history
          SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
              PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
          WHERE idHist = new.idHist;
      END
[2023-10-30 15:17:45] completed in 3 ms
6

Try to execute it by ->execute() method as ->query() is intended to execute select queries only.

See https://www.yiiframework.com/doc/api/2.0/yii-db-command#execute()-detail

7

Ok, i’ve changed the execution to execute() method instead query() but the result is the same as above

 $triggerSql = '
            CREATE TRIGGER new_id_person
            AFTER INSERT ON history
            FOR EACH ROW
            WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
            BEGIN
                UPDATE history
                SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
                    PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
                WHERE idHist = new.idHist;
            END;
        ';

        Yii::$app->db->createCommand($triggerSql)->execute();

Result

    > execute SQL: DROP TRIGGER IF EXISTS new_id_person ... done (time: 0.000s)
Exception 'yii\db\Exception' with message 'SQLSTATE[HY000]: General error: 1 incomplete input
Failed to prepare SQL: CREATE TRIGGER new_id_person
            AFTER INSERT ON history
            FOR EACH ROW
            WHEN (new.cardType > -1) AND (EXISTS(SELECT cardNo FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType))
            BEGIN
                UPDATE history
                SET idPerson = (SELECT idPerson FROM karty WHERE cardNo = new.cardNo AND cardType = new.cardType),
                    PersonName = (SELECT name FROM vkarty WHERE cardNo = new.cardNo AND cardType = new.cardType)
                WHERE idHist = new.idHist;'

in /Users/xxx/Dropbox/xxx-SITES/YII-RCP/YII2-APP/vendor/yiisoft/yii2/db/Command.php:272

Error Info:
Array
(
    [0] => HY000
    [1] => 1
    [2] => incomplete input
)

Caused by: Exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 1 incomplete input'

in /Users/xxx/Dropbox/xxx-SITES/YII-RCP/YII2-APP/vendor/yiisoft/yii2/db/Command.php:267
8

Can you share DDL script for the table and migration script for one to test and see?