Laravel 5.3 – Creating MySQL Triggers from Migration with example

Laravel 5.3 – Creating MySQL Triggers from Migration with example

In this tutorial, you will learn how to work with MySQL in Laravel 5.

Trigger is what it is only stored program and once you write a trigger against any events then whenever that events occur then triggers are automatically executed.

Normally we write DML triggers that means whenever user want to insert update or delete anything from MySQL database then DML triggers fire.

To add triggers to database schema, we write create statement like this :

CREATE
    TRIGGER 'trigger_name' BEFORE/AFTER INSERT/UPDATE/DELETE
    ON 'database'.'table-name'
    FOR EACH ROW BEGIN
		-- trigger body
		-- write query that will be executed against specified events
		-- inserted/updated/deleted row
    END;

In Laravel, we will use DB::unprepared() method to write triggers query to add triggers to database schema.

I am going to create triggers from migration file so first i need to create migration file using following command :

php artisan make:migration create_trigger

Now you will see a migration file in database/migrations directory, open this migration file and add following code :

Migration: CreateTrigger

I create a trigger to insert user role id to assign user with default role whenever new user will be inserted.

<?php
use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;
class CreateTrigger extends Migration
{
    public functionup()
{
          DB::unprepared('CREATE TRIGGER user_default_role AFTER INSERT ON 'users' FOR EACH ROW
                BEGIN
                   INSERT INTO 'user_role' ('role_id', 'user_id', 'created_at', 'updated_at') VALUES (3, NEW.id, now(), null);
                END');
}
    public functiondown()
{
       DB::unprepared('DROP TRIGGER 'user_default_role'');
}
}

<?php

use IlluminateSupportFacadesSchema;
use IlluminateDatabaseSchemaBlueprint;
use IlluminateDatabaseMigrationsMigration;

class CreateTrigger extends Migration
{
    public function up()
    {
          DB::unprepared('CREATE TRIGGER user_default_role AFTER INSERT ON 'users' FOR EACH ROW
                BEGIN
                   INSERT INTO 'user_role' ('role_id', 'user_id', 'created_at', 'updated_at') VALUES (3, NEW.id, now(), null);
                END');
    }
    public function down()
    {
       DB::unprepared('DROP TRIGGER 'user_default_role'');
    }
}

Run Migration:

php artisan migrate