Laravel 4 database statement create trigger

Question

During the creation process of my app's migration files, I noticed that Laravel does not support database triggers! I have come to terms with the fact that I need to execute a query statement to accomplish this, but that is also giving me troubles.. Here is a code snippet from my app:

Schema::create('users', function ($table) {
    $table->increments('id');
    $table->string('uuid', 36);
    $table->string('email', 255);
    $table->string('password', 255);
});
DB::statement('CREATE TRIGGER users_trigger_uuid BEFORE INSERT ON users FOR EACH ROW SET NEW.uuid = UUID()');

When I run artisan migrate it gives me the following error:

[Exception]
SQLSTATE[HY000]: General error: 2030 This command is not supported in the prepared statement protocol yet (SQL: CREATE TRIGGER users_trigger_uuid BEFORE INSERT ON users FOR EACH ROW SET NEW.uuid = UUID()) (Bindings: array ( ))

Are there any solutions apart from creating my own PDO object and executing the query outside of Laravel? Is this a MySQL exception or a Laravel exception?

EDIT

Judging by the exception, it's clear that prepared statements don't support the creation of triggers.. not sure why but would love some insight. To get around this I just ran my own PDO query.

$default_driver = Config::get('database.default');
$connection_info = Config::get('database.connections.' . $default_driver);
$conn = new PDO('mysql:host=' . $connection_info['host'] . ';dbname=' . $connection_info['database'], $connection_info['username'], $connection_info['password']);
$conn->query('CREATE TRIGGER users_trigger_uuid BEFORE INSERT ON ' . $connection_info['prefix'] . 'users FOR EACH ROW SET NEW.uuid = UUID()');

Answer 1

Use DB::unprepared(), works like a charm.

Answer 2

The "clean" way to do this is to use database events. When you create a new User instance, Laravel4 fires a created event.

Eloquent models fire several events, allowing you to hook into various points in the model's lifecycle using the following methods: creating, created, updating, updated, saving, saved, deleting, deleted.

Whenever a new item is saved for the first time, the creating and created events will fire. If an item is not new and the save method is called, the updating / updated events will fire. In both cases, the saving / saved events will fire.

So, inside your User class, add a listener for the creating event:

class User extends Eloquent ... {

    // ...

    public static function boot()
    {
        parent::boot();
        static::creating(function($user)
        {
            $user->uuid = uuid();
        });
    }

For how to generate a UUID in PHP, see this answer.

Answer 3

The solution you show in your edit is not correct. You should either use DB::unprepared(), or, if you must use PDO, do not do it that way. Instead, get a PDO object from the connection and use that:

$pdo = DB::connection()->getPdo();