Laravel, create MySQL trigger from Migration

Goal

Create a booking system where each new booking gets a unique auto-generated bkg_id in the format BKG-00000001, BKG-00000002, and so on.

Step 1: Create the Model and Migration

Run this command to create a model and migration for the sequence table:

php artisan make:model SequenceTbl -m

Step 2: Define Your Migrations

`database/migrations/YYYY_MM_DD_create_sequence_tbls_table.php`

<?php

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

class CreateSequenceTblsTable extends Migration
{
    public function up()
    {
        Schema::create('sequence_tbls', function (Blueprint $table) {
            $table->id(); // Auto-incrementing primary key
        });
    }

    public function down()
    {
        Schema::dropIfExists('sequence_tbls');
    }
}

`database/migrations/YYYY_MM_DD_create_bookings_table.php`

<?php

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

class CreateBookingsTable extends Migration
{
    public function up()
    {
        Schema::create('bookings', function (Blueprint $table) {
            $table->id();
            $table->string('bkg_id'); // Custom generated Booking ID
            $table->string('name')->nullable();
            $table->string('room_type')->nullable();
            $table->string('total_numbers')->nullable();
            $table->string('date')->nullable();
            $table->string('time')->nullable();
            $table->string('arrival_date')->nullable();
            $table->string('depature_date')->nullable();
            $table->string('email')->nullable();
            $table->string('ph_number')->nullable();
            $table->string('status')->nullable();
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('bookings');
    }
}

`database/migrations/YYYY_MM_DD_create_generate_id_trigger.php`

This migration sets up a MySQL trigger to generate the bkg_id.

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;

class CreateCreateGenerateIDBookingsTable extends Migration
{
    public function up()
    {
        DB::unprepared('
            CREATE TRIGGER id_store BEFORE INSERT ON bookings
            FOR EACH ROW
            BEGIN
                INSERT INTO sequence_tbls VALUES (NULL);
                SET NEW.bkg_id = CONCAT("BKG-", LPAD(LAST_INSERT_ID(), 8, "0"));
            END
        ');
    }

    public function down()
    {
        DB::unprepared('DROP TRIGGER IF EXISTS id_store');
    }
}

Step 3: Run the Migrations

After creating all the migrations, run this command:

php artisan migrate

This will:

Create the sequence_tbls table to track the ID sequence
Create the bookings table
Add a trigger to automatically generate a formatted bkg_id

Notes

The trigger uses LAST_INSERT_ID() to fetch the latest ID inserted into sequence_tbls, then pads it to 8 digits and prefixes it with BKG-.
Make sure your MySQL user has permission to create triggers.
This solution doesn't rely on Laravel logic for generating the ID — it's handled directly by MySQL.

Example: Inserting a Booking

You can test by inserting a new booking record using Laravel’s tinker or a controller:

// In a controller or route
Booking::create([
    'name' => 'John Doe',
    'room_type' => 'Deluxe',
    'total_numbers' => '3',
    'date' => '2025-04-05',
    'time' => '10:00 AM',
    'arrival_date' => '2025-04-10',
    'depature_date' => '2025-04-15',
    'email' => 'john@example.com',
    'ph_number' => '1234567890',
    'status' => 'pending',
]);

After creation, the the bkg_id field will look like this: BKG-00000001

Let me know if you'd like:

A full CRUD controller
Postman collection to test
Blade UI or API setup

Souy Soeng