Skip to content

Instantly share code, notes, and snippets.

@michabbb
Last active April 18, 2021 12:30
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save michabbb/377deb85958a50d06b1aee5d9ba4a790 to your computer and use it in GitHub Desktop.
Save michabbb/377deb85958a50d06b1aee5d9ba4a790 to your computer and use it in GitHub Desktop.
In case u never use laravel timestamp columns in your models
<?php
namespace App\Console\Commands\helper;
use DB;
use Doctrine\DBAL\Exception;
use Illuminate\Console\Command;
class db_create_triggers extends Command
{
/**
* The name and signature of the console command.
*
* @var string
*/
protected $signature = 'db:create-triggers';
/**
* The console command description.
*
* @var string
*/
protected $description = 'Command description';
/**
* Execute the console command.
*
* @return int
* @throws Exception
*/
public function handle(): int
{
$tables = DB::connection()->getDoctrineSchemaManager()->listTableNames();
foreach ($tables as $table) {
$columns = DB::select('SELECT
c.COLUMN_NAME,c.COLUMN_KEY
FROM information_schema.COLUMNS c
WHERE c.TABLE_SCHEMA = ?
AND c.TABLE_NAME = ?',[config('database.connections.mysql.database'),$table]);
$columns_parsed = collect();
foreach ($columns as $column) {
$columns_parsed[$column->COLUMN_NAME] = ($column->COLUMN_KEY==='PRI') ? 1 : 0;
}
// d($columns_parsed);
$primary_key = $columns_parsed->filter(fn($value,$key) => $value === 1)->keys()->get(0);
if ($primary_key) {
$triggers = DB::select("SELECT
t.TRIGGER_NAME,
t.EVENT_MANIPULATION,
t.ACTION_TIMING,
t.EVENT_OBJECT_TABLE
FROM information_schema.TRIGGERS t
WHERE t.TRIGGER_SCHEMA = '".config('database.connections.mysql.database')."'
AND t.EVENT_OBJECT_TABLE = '".$table."'");
$triggers_parsed = collect();
foreach ($triggers as $trigger) {
$triggers_parsed[$trigger->TRIGGER_NAME] = $trigger->ACTION_TIMING.'_'.$trigger->ACTION_TIMING;
}
$splitted_pk = explode('_', $primary_key);
$table_prefix = $splitted_pk[0];
if ($columns_parsed->has($table_prefix . '_db_changed') && !$triggers_parsed->has('tr_bu_' . $table_prefix)) {
$this->info('create trigger BEFORE UPDATE for table: '.$table);
DB::statement("CREATE
TRIGGER tr_bu_".$table_prefix."
BEFORE UPDATE
ON ".$table."
FOR EACH ROW
BEGIN
SET NEW.".$table_prefix."_db_changed = NOW();
END");
}
if ($columns_parsed->has($table_prefix . '_db_created') && !$triggers_parsed->has('tr_bi_' . $table_prefix)) {
$this->info('create trigger BEFORE INSERT for table: '.$table);
DB::statement("CREATE
TRIGGER tr_bi_".$table_prefix."
BEFORE INSERT
ON ".$table."
FOR EACH ROW
BEGIN
SET NEW.".$table_prefix."_db_created = NOW();
END");
}
}
}
return 0;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment