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()');

whilst the answer you have selected is simple and 'eloquent' it doesn't address a serious database integrity issue. You should try and wrap the database code in a transaction so that if anything fails, it all fails. — GWed, Feb 14 '14 at 09:22

score 8 · Answer 1 · answered Apr 21 '13 at 21:17

8

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

answered Apr 21 '13 at 21:17

LaravelLoser

89
1

score 4 · Answer 2 · edited May 23 '17 at 12:09

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.

need to be very careful using this solution. It doesn't appear like the database modifying code is wrapped in a transaction. Your database could end up in an awful state if you aren't careful. — GWed, Feb 14 '14 at 09:21
Creation ought to be atomic; here we are modifying a *tuple* before it is actually `INSERT`ed. It is however possible to transact statements by modifying or overriding `"vendor/laravel/framework/src/Illuminate/Database/Connection.php` `run()` method. — LSerni, Feb 14 '14 at 23:12

score 2 · Answer 3 · answered Jun 28 '13 at 14:26

2

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();

answered Jun 28 '13 at 14:26

Mike Holler

945
2
13
28

Laravel 4 database statement create trigger

3 Answers3