Add database trigger to laravel migrations

Laravel Database Trigger provides a way to add database trigger to laravel migrations just like you would with database table. A trigger is a named database object that is associated with a table, and that activates when a particular event occurs for the table. Read more about triggers here.

Installation

Laravel Database Trigger requires at least PHP 7.2. This particular version supports laravel at least v6.0 The package currently supports MySQL only.

To get the latest version, simply require the package using Composer:

$ composer require sweetmancc/mysql-trigger-for-laravel-migration

Once installed, if you are not using automatic package discovery, then you need to register the Sweetmancc\DatabaseTrigger\TriggerServiceProvider service provider in your config/app.php.

Usage

Create a trigger migration file using the make:trigger artisan command. The command requires the name of the trigger, name of the event object table, action timing and the event that activates the trigger.

$ php artisan make:trigger after_users_posts_insert

Event object table

The event object table is the name of the table the trigger is associated with.

Action timing

The activation time for the trigger. Possible values are after and before.

after - Process action after the change is made on the event object table.

before - Process action prior to the change is made on the event object table.

Event

The event to activate trigger. A trigger event can be insert, update and delete.

insert - Activate trigger when an insert operation is performed on the event object table.

update - Activate trigger when an update operation is performed on the event object table.

delete - Activate trigger when a delete operation is performed on the event object table.

The following trigger migration file will be generated for a trigger that uses after_users_posts_insert as a name, user_posts as event object table name, after as action timing and insert as event.

use Illuminate\Database\Migrations\Migration;
use Sweetmancc\DatabaseTrigger\TriggerFacade as Schema;

class CreateAfterUsersUpdateTrigger extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('after_users_posts_insert')
            ->on('user_posts')
            ->statement(function() {
                return '//You logic, Don't forget ";" ';
            })
            ->after()
            ->update();
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('users.after_users_posts_insert');
    }
}

Return the trigger statement from the closure of the statement method.

The following is an example trigger migration to insert into the users_audit table after updating a user row.

...

    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('after_user_posts_insert')
            ->on('user_posts')
            ->statement(function() {
                //It means that when insert an row into user_posts table It will inrease 1 to user_profiles table where row record user_id same as NEW insert to user_posts table user_id field.
                return 'UPDATE user_profiles SET postCount = postCount + 1 WHERE id = NEW.user_id;';
            })
            ->after()
            ->update();
    }

...

Update your database migration

php artisan migrate

My mysql Database Tables user_posts & user_profiles

CREATE TABLE `user_posts` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `user_id` bigint(20) unsigned NOT NULL,
  `text` text COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `user_posts_user_id_foreign` (`user_id`),
  CONSTRAINT `user_posts_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `user_profiles` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `postCount` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_profiles_user_id_unique` (`user_id`),
  CONSTRAINT `user_profiles_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Testing

Run the tests with:

$ composer test

Changelog

Please see CHANGELOG for more information on what has changed recently.

Contributing

Please see CONTRIBUTING for details.

Security

If you discover a security vulnerability within this package, please send an e-mail to Andy Q at [email protected]. All security vulnerabilities will be promptly addressed.

License

Laravel Database Trigger is licensed under The MIT License (MIT).