How to use triggers in laravel?

Question

My code, using PHP artisan make: migration create_trigger command

public function up()
{
  DB::unprepared(' 
  CREATE TRIGGER roll_no BEFORE INSERT ON `students` FOR EACH ROW 
  BEGIN 
  SET @roll_num = IFNULL((substring((SELECT student_roll_no FROM students WHERE
    class_code = NEW.class_code ORDER BY created_at DESC LIMIT 1),-2) + 1), `1`),
    NEW.student_roll_no = CONCAT(
    YEAR(CURRENT_DATE),
    NEW.class_code,
    IF (@roll_num < 10, CONCAT(`0`, @roll_num), @roll_num) 
  )
  END;
  ');
}

Solution 1

You don't need to create a migration for a model event. Laravel eloquent has multiple events such as retrieved, creating, created, updating, updated, saving, saved, deleting, deleted, restoring, restored that you can easily use them.

first, you should create Observer for your model like this

php artisan make:observer UserObserver --model=User

in the UserObserver you can listen to any event that you like such as:

class UserObserver
{
    /**
     * Handle the User "created" event.
     *
     * @param  \App\User  $user
     * @return void
     */
    public function created(User $user)
    {
        //
    }

    /**
     * Handle the User "updated" event.
     *
     * @param  \App\User  $user
     * @return void
     */
    public function updated(User $user)
    {
        //
    }
}

after that you should register your observer to model in app/providers/AppServiceProvider boot method such as:

public function boot()
{
     User::observe(UserObserver::class);
}

for more detail visit Laravel documentation.

Solution 2

try this: please check your SQL syntax

DB::unprepared('CREATE TRIGGER roll_no BEFORE INSERT ON `students` FOR EACH ROW
        BEGIN
            SET @roll_num = IFNULL((substring((SELECT student_roll_no FROM students WHERE class_code = NEW.class_code ORDER BY created_at DESC LIMIT 1),-2) + 1), `1`),
            NEW.student_roll_no = CONCAT(YEAR(CURRENT_DATE)),
            NEW.class_code,
            IF (@roll_num < 10,
                CONCAT(`0`, @roll_num),
                @roll_num
            )
        END');

for example please check this link :

[https://itsolutionstuff.com/post/how-to-add-mysql-trigger-from-migrations-in-laravel-5example.html]1

i hope help you

Solution 3

For this type of operation, laravel makes some technique similar to trigger(event). If you have multiple events then use Observer otherwise you can choose this solution.

// inside your model

public function boot()
{
    parent::boot();

    // beforeCreate
    self::creating(function($model) {
        // do something with your $model before saving....

        // return true or the save will cancel....
        return true;
    });


    // afterCreate
    self::created(function($model) {
        // do something with your $model after saving....

        // return true or the save will cancel....
        return true;
    });


}