Laravel, create MySQL trigger from Migration

Question

I have created MySQL stored procedure from migration and it works just fine.

DB::unprepared('
    CREATE PROCEDURE sp_Create_Default_Task_1(IN _kid_id INT)
    BEGIN
        INSERT INTO tasks (kid_id, name) VALUES (_kid_id, \'daily\');
    END'
    );

Hereafter I tried to do the same to create MySQL trigger with following code

<?php

use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTrigger extends Migration {

    public function up()
    {
        DB::unprepared('
        CREATE TRIGGER tr_Task_Default AFTER INSERT ON `kids` FOR EACH ROW
            INSERT INTO tasks (`kid_id`, `name`) VALUES (NEW.id, \'Default\');
        ');
    }


    public function down()
    {
        DB::unprepared('DROP TRIGGER `tr_User_Default_Member_Role`');
    }
}

But it returns error after I run php artisan migrate

{"error":{"type":
"Symfony\\Component\\Debug\\Exception\\FatalErrorException",
"message":"Class 'CreateTriggers' not found",
"file":"C:\\xampp\\htdocs\\dev03\\vendor\\laravel\\framework
\\src\\Illuminate\\Database\\Migrations\\Migrator.php",
"line":301}}

Question: What is going wrong?

Solution 1

There was issue with class naming.

Correct class name could help OR do as I did, Copy your working trigger code temporary in notepad/text. Delete the old migration trigger file and generate new one.

Note: By the way the same solution is valid for Laravel 4.x and Laravel 5.x

In Laravel 4

php artisan generate:migration create_trigger

In Laravel 5

php artisan make:migration create_trigger

After it was generated I copy and paste the same Trigger code from my notepad/text and it works just fine.

Here is the final working code for creating trigger through migration.

it works both with RAW and UNPREPARED method.

<?php

use Illuminate\Database\Migrations\Migration;

class CreateTrigger extends Migration {

    public function up()
    {
        DB::unprepared('
        CREATE TRIGGER tr_User_Default_Member_Role AFTER INSERT ON `users` FOR EACH ROW
            BEGIN
                INSERT INTO role_user (`role_id`, `user_id`, `created_at`, `updated_at`) 
                VALUES (3, NEW.id, now(), null);
            END
        ');
    }

    public function down()
    {
        DB::unprepared('DROP TRIGGER `tr_User_Default_Member_Role`');
    }
}

Note: This is just example to demonstrate the concept

Solution 2

Run composer dumpautoload in the root (the same place as artisan) should make it work.

Solution 3

In Laravel 5.5 works only with DB::unprepared() method.

Comments

[Laurence]

Post the whole file. Looks like your class name is wrong?

[Maytham Fahmi]

I have solved it now, thx it was some thing with the class name

[Reiah Paul Sam]

For, me the migration for creating trigger runs successfully but i couldn't find the trigger in the database. can you please help

[bishop]

In my experience the DB::unprepared(...) worked for me