Adding Triggers to the Blueprint API

[BPowell76]

I am currently working on a little project and noticed that there doesn't seem to be an option to create custom database triggers. If I am looking in the wrong place, please let me know, but I think this would be a great feature because it offloads some of the work the framework would need to do to the database server/application.

Common use cases I have for database triggers are:

• Automatically setting a date updated
• Updating values inserted into the database to fit a specific format

For SQL Server you would create a trigger like so to automatically set the updated_at datetime:

CREATE TRIGGER trig_update_example
ON [schema].[table]
AFTER UPDATE
AS

SET NOCOUNT ON;

IF TRIGGER_NESTLEVEL() > 1
RETURN;

UPDATE [schema].[table]
SET updated_at = GETDATE()
WHERE id IN (SELECT id FROM inserted);

For Postgres it's a bit more convoluted, but you would do something like the following:

CREATE TRIGGER [trigger_name] AFTER UPDATE
ON [table_name]
FOR EACH ROW
EXECUTE FUNCTION [function_name]();

CREATE FUNCTION [function_name]() RETURNS TRIGGER AS $[trigger_name]$
BEGIN
  NEW.updated_at := CURRENT_TIMESTAMP;
  RETURN NEW;
END;
$[trigger_name]$ LANGUAGE plpgsql;

[Sandalanka]

Laravel itself doesn’t have built-in support for defining triggers via migrations, but there are a few good ways to implement them cleanly while still keeping your migrations portable and version-controlled.

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

return new class extends Migration {
public function up(): void
{
DB::unprepared("
CREATE TRIGGER trig_update_example
ON [schema].[table]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
IF TRIGGER_NESTLEVEL() > 1 RETURN;
UPDATE [schema].[table]
SET updated_at = GETDATE()
WHERE id IN (SELECT id FROM inserted);
END
");
}

public function down(): void
{
    DB::unprepared("DROP TRIGGER IF EXISTS trig_update_example ON [schema].[table];");
}

};
Option 2: Use a Package

example packages :
laravel-migrations-generator/triggers
yajra/laravel-pdo-via-oci8 (for Oracle-like triggers)
staudenmeir/laravel-migrator-triggers

[BPowell76]

Thanks. I'll check out DB::unprepared(). Didn't know it was a thing.