1

I have inherited a project. They have used a database seeder. They have this method;

private function cleanDatabase()
    {
        foreach ($this->tables as $tableName) {
            DB::statement("ALTER TABLE $tableName DISABLE TRIGGER ALL");
            DB::statement("TRUNCATE TABLE $tableName CASCADE");
            DB::statement("ALTER TABLE $tableName ENABLE TRIGGER ALL");
        }
    }

When I run the php artisan db:seed I get the error;

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER ALL' at line 1

This is the only script with it in, so something must be wrong here. I have never used DISABLE TRIGGER ALL in my life so not even sure what it does.

mikelovelyuk
  • 4,042
  • 9
  • 49
  • 95

2 Answers2

0

It seems this is MS SQL syntax. You should probably look at MySQL disable all triggers and use MySQL syntax to achieve that.

Community
  • 1
  • 1
Marcin Nabiałek
  • 109,655
  • 42
  • 258
  • 291
0

I had to take the long way out, since there doesn't seem to be another way:

drop the trigger, run the migration, recreate the trigger.

DB::statement('DROP TRIGGER IF EXISTS `contract_quantities_after_update`');
DB::statement('UPDATE contract_quantities SET unit_price=(SELECT unit_price FROM contracts WHERE id=contract_quantities.contract_id)');
DB::statement('CREATE TRIGGER `contract_quantities_after_update` AFTER UPDATE ON `contract_quantities` FOR EACH ROW UPDATE contracts SET quantity=(SELECT IFNULL(SUM(quantity),0) FROM contract_quantities WHERE contract_id=NEW.contract_id) WHERE id=NEW.contract_id');
JoeGalind
  • 3,545
  • 2
  • 29
  • 33