Skip to content
YajraBox

Laravel Oci8
Icon

WARNING You're browsing the documentation for an upcoming version of Laravel Oci8. The documentation and features of this release are subject to change.

Oracle Triggers

Oracle triggers are database objects that automatically execute in response to certain events on a table. Laravel-OCI8 provides a simple API for managing triggers.

Accessing the Trigger Manager

$trigger = DB::getTrigger();

Available Methods

Create an Auto-Increment Trigger

Creates a trigger that automatically assigns the next sequence value to a column on insert:

$trigger->autoIncrement($table, $column, $triggerName, $sequenceName);

Example:

$trigger->autoIncrement(
    'users',           // Table name
    'id',              // Column to auto-increment
    'users_id_trg',     // Trigger name
    'users_id_seq'     // Sequence name
);

This creates a trigger similar to:

CREATE OR REPLACE TRIGGER users_id_trg
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SELECT users_id_seq.NEXTVAL INTO :NEW.id FROM DUAL;
END;

Drop a Trigger

$trigger->drop($triggerName);

Example:

$trigger->drop('users_id_trg');

Complete Example: Setting Up Auto-Increment

Here's a complete example of manually creating the sequence and trigger:

Step 1: Create the Sequence

$sequence = DB::getSequence();
$sequence->create('posts_id_seq', $start = 1);

Step 2: Create the Trigger

$trigger = DB::getTrigger();
$trigger->autoIncrement('posts', 'id', 'posts_id_trg', 'posts_id_seq');

Step 3: Use the Table

Now when you insert records, the ID is automatically generated:

DB::connection('oracle')->table('posts')->insert([
    'title' => 'My First Post',
    'content' => 'Post content here...',
]);
 
// Or with Eloquent
$post = new Post();
$post->title = 'My First Post';
$post->content = 'Post content here...';
$post->save();
 
// $post->id is now automatically set

Manual Trigger Creation

For more complex trigger requirements, create them manually in a migration:

<?php
 
use Illuminate\Database\Migrations\Migration;
use Illuminate\Support\Facades\DB;
 
class CreatePostsTrigger extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        $sql = "
            CREATE OR REPLACE TRIGGER posts_id_trg
            BEFORE INSERT ON posts
            FOR EACH ROW
            WHEN (NEW.id IS NULL)
            BEGIN
                SELECT posts_id_seq.NEXTVAL INTO :NEW.id FROM DUAL;
            END;
        ";
 
        DB::connection()->getPdo()->exec($sql);
    }
 
    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::connection()->getPdo()->exec("DROP TRIGGER posts_id_trg");
    }
}

Trigger Events

Oracle triggers can fire at different times:

Timing Description
BEFORE Executes before the triggering event
AFTER Executes after the triggering event
INSTEAD OF Replaces the triggering event (for views)

Trigger Levels

Level Description
FOR EACH ROW Row-level trigger - executes once per affected row
Statement-level Executes once per statement

See Also

Edit this page on GitHub