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 :
I create a trigger to insert user role id to assign user with default role whenever new user will be inserted.
- use Illuminate\Support\Facades\Schema;
- use Illuminate\Database\Schema\Blueprint;
- use Illuminate\Database\Migrations\Migration;
- class CreateTrigger extends Migration
- public function up()
- DB::unprepared('CREATE TRIGGER user_default_role AFTER INSERT ON `users` FOR EACH ROW
- INSERT INTO `user_role` (`role_id`, `user_id`, `created_at`, `updated_at`) VALUES (3, NEW.id, now(), null);
- public function down()
- DB::unprepared('DROP TRIGGER `user_default_role`');
php artisan migrate