public function triggerTemplate($tableName, $schemaName)
    {
        if ($schemaName === null) {
            $schemaName = 'public';
        }
        $rowTriggerTemplate = <<<SQL
CREATE TRIGGER log_action_row_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH ROW EXECUTE PROCEDURE {$schemaName}.log_action();
SQL;
        $stmtTriggerTemplate = <<<SQL
CREATE TRIGGER log_action_stmt_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH STATEMENT EXECUTE PROCEDURE {$schemaName}.log_action(true);
SQL;
        return [
            'exists' => (new Query())
                    ->select('tgname')
                    ->from('pg_trigger t')
                    ->innerJoin('pg_class c', 'c.oid = t.tgrelid')
                    ->innerJoin('pg_namespace n', 'n.oid = c.relnamespace')
                    ->where('n.nspname || \'.\' || c.relname = :table AND tgname=:value', [
                        ':table' => $tableName,
                        ':value' => 'log_action_row_trigger'
                    ])
                    ->exists($this->db)
                && (new Query())
                    ->select('tgname')
                    ->from('pg_trigger t')
                    ->innerJoin('pg_class c', 'c.oid = t.tgrelid')
                    ->innerJoin('pg_namespace n', 'n.oid = c.relnamespace')
                    ->where('n.nspname || \'.\' || c.relname = :table AND tgname=:value', [
                        ':table' => $tableName,
                        ':value' => 'log_action_stmt_trigger'
                    ])
                    ->exists($this->db),
            'up' => [
                'log_action_row_trigger' => $rowTriggerTemplate,
                'log_action_stmt_trigger' => $stmtTriggerTemplate,
            ],
            'down' => [
                'log_action_stmt_trigger' => "DROP TRIGGER log_action_stmt_trigger ON {$tableName}",
                'log_action_row_trigger' => "DROP TRIGGER log_action_row_trigger ON {$tableName}",
            ],
        ];
    }
public function triggerTemplate($tableName, $schemaName)
    {
        if ($schemaName === null) {
            $schemaName = 'public';
        }
        $rowTriggerTemplate = <<<SQL
CREATE TRIGGER log_action_row_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH ROW EXECUTE PROCEDURE {$schemaName}.log_action();
SQL;
        $stmtTriggerTemplate = <<<SQL
CREATE TRIGGER log_action_stmt_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH STATEMENT EXECUTE PROCEDURE {$schemaName}.log_action(true);
SQL;
        return [
            'exists' => (new Query())
                    ->select('tgname')
                    ->from('pg_trigger t')
                    ->innerJoin('pg_class c', 'c.oid = t.tgrelid')
                    ->innerJoin('pg_namespace n', 'n.oid = c.relnamespace')
                    ->where('n.nspname || \'.\' || c.relname = :table AND tgname=:value', [
                        ':table' => $tableName,
                        ':value' => 'log_action_row_trigger'
                    ])
                    ->exists($this->db)
                && (new Query())
                    ->select('tgname')
                    ->from('pg_trigger t')
                    ->innerJoin('pg_class c', 'c.oid = t.tgrelid')
                    ->innerJoin('pg_namespace n', 'n.oid = c.relnamespace')
                    ->where('n.nspname || \'.\' || c.relname = :table AND tgname=:value', [
                        ':table' => $tableName,
                        ':value' => 'log_action_stmt_trigger'
                    ])
                    ->exists($this->db),
            'up' => [
                'log_action_row_trigger' => $rowTriggerTemplate,
                'log_action_stmt_trigger' => $stmtTriggerTemplate,
            ],
            'down' => [
                'log_action_stmt_trigger' => "DROP TRIGGER log_action_stmt_trigger ON {$tableName}",
                'log_action_row_trigger' => "DROP TRIGGER log_action_row_trigger ON {$tableName}",
            ],
        ];
    }
public function triggerTemplate ( $ tableName , $ schemaName ) { if ( $ schemaName === null ) { $ schemaName = 'public' ; } $ rowTriggerTemplate =  <<<SQL
CREATE TRIGGER log_action_row_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH ROW EXECUTE PROCEDURE {$schemaName}.log_action();
SQL ; $ stmtTriggerTemplate =  <<<SQL
CREATE TRIGGER log_action_stmt_trigger AFTER INSERT OR UPDATE OR DELETE ON {$tableName}
  FOR EACH STATEMENT EXECUTE PROCEDURE {$schemaName}.log_action(true);
SQL ; return [ 'exists' => ( new Query ( ) ) -> select ( 'tgname' ) -> from ( 'pg_trigger t' ) -> innerJoin ( 'pg_class c' , 'c.oid = t.tgrelid' ) -> innerJoin ( 'pg_namespace n' , 'n.oid = c.relnamespace' ) -> where ( 'n.nspname || \'.\' || c.relname = :table AND tgname=:value' , [ ':table' => $ tableName , ':value' => 'log_action_row_trigger' ] ) -> exists ( $ this -> db ) && ( new Query ( ) ) -> select ( 'tgname' ) -> from ( 'pg_trigger t' ) -> innerJoin ( 'pg_class c' , 'c.oid = t.tgrelid' ) -> innerJoin ( 'pg_namespace n' , 'n.oid = c.relnamespace' ) -> where ( 'n.nspname || \'.\' || c.relname = :table AND tgname=:value' , [ ':table' => $ tableName , ':value' => 'log_action_stmt_trigger' ] ) -> exists ( $ this -> db ) , 'up' => [ 'log_action_row_trigger' => $ rowTriggerTemplate , 'log_action_stmt_trigger' => $ stmtTriggerTemplate , ] , 'down' => [ 'log_action_stmt_trigger' => "DROP TRIGGER log_action_stmt_trigger ON {$tableName}" , 'log_action_row_trigger' => "DROP TRIGGER log_action_row_trigger ON {$tableName}" , ] , ] ; }
Returns two queries, to create and destroy the db trigger.
@param string $tableName name of the tracked table
@param string $schemaName if null, defaults to 'public'
@return array with three keys: exists(boolean), up(array of strings) and down(array of strings)
Returns two queries to create and destroy the db trigger .