DISABLE TRIGGER ALL with Laravel DB Seeding Error

Question

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.

score 0 · Answer 1 · edited May 23 '17 at 11:59

0

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

edited May 23 '17 at 11:59

Community

1
1

answered Jan 11 '16 at 20:21

Marcin Nabiałek

109,655
42
258
291

score 0 · Answer 2 · answered Feb 14 '20 at 15:02

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');

DISABLE TRIGGER ALL with Laravel DB Seeding Error

2 Answers2