Triggers and Statistics in SQL Server

[junior-paytech]

I am working on a project where database has both triggers and statistics and when I try to drop trigger and after that drop statistics I got an error that I don't have permission or statistcs does not exists.

I am using "sa" user and the same sql script works when running it on SQL Express or other DBMS.

Does anybody have any suggestion?

I also created a "Base migration" that extends AbstractMigration to implement the methods to check for statistics

<?php

declare(strict_types=1);

namespace App\Infrastructure\Database\Migrations;

use Phinx\Migration\AbstractMigration;

abstract class BaseMigration extends AbstractMigration
{
    public function getStatistics(string $tableName, string $columnName)
    {
        return $this->query("SELECT
                    s.name AS statistics_name,
                    c.name AS column_name,
                    t.name AS table_name
                FROM sys.stats AS s
                JOIN sys.stats_columns AS sc ON s.stats_id = sc.stats_id AND s.object_id = sc.object_id
                JOIN sys.columns AS c ON sc.object_id = c.object_id AND sc.column_id = c.column_id
                JOIN sys.tables AS t ON s.object_id = t.object_id
                WHERE
                    t.name = '{$tableName}' AND
                    c.name = '{$columnName}'")->fetchAll(\PDO::FETCH_OBJ);
    }

    /**
     * Check if the informed table.column has statistics
     * @param string $tableName
     * @param string $columnName
     * @return bool
     */
    public function hasStatistic(string $tableName, string $columnName): bool
    {
        return count($this->getStatistics($tableName, $columnName)) > 0;
    }

    public function dropStatistic(string $tableName, string $columnName)
    {
        $return = true;
        $statistics = $this->getStatistics($tableName, $columnName);
        foreach ($statistics as $statistic) {
            if ($statistic->table_name === $tableName && $statistic->column_name === $columnName) {
                $this->query("DROP STATISTICS {$tableName}.{$columnName}")->execute();
            }
        }

        return $return;
    }

    public function createStatistic(string $tableName, string $columnName)
    {
        return $this->query("CREATE STATISTICS {$columnName} ON {$tableName}({$columnName})")->execute();
    }
}

[MasterOdin]

Can you share a migration that creates a trigger and statistic, as well as the migration that has the issue?

[junior-paytech]

Hi @MasterOdin , below the migration code.

I just changed some table / column names to "ofuscate" original data

<?php
declare(strict_types=1);
require 'BaseMigration.php';
final class UpdatePFWarningsRemoveRelatedId extends BaseMigration
{
    private $warningTable = 'PF_Warnings';
    private $logWarningsTable = 'TAU_PF_Warnings';
    public function up(): void
    {
        $this->dropTriggersIfExists();
        $this->dropRelatedIdColumn();
        $this->createTriggersWithoutRelatedId();
    }
    public function down(): void
    {
        $this->dropTriggersIfExists();

        $this->createRelatedIdColumn();

        $this->createTriggersWithRelatedId();
    }

    private function dropTriggersIfExists()
    {
        $this->query("
            IF OBJECT_ID('TAU_DEL_PF_Warnings', 'TR') IS NOT NULL
            BEGIN
                DROP TRIGGER TAU_DEL_PF_Warnings;
            END
        ")->execute();
        $this->query("
        IF OBJECT_ID('TAU_UPD_PF_Warnings', 'TR') IS NOT NULL
            BEGIN
                DROP TRIGGER TAU_UPD_PF_Warnings;
            END
        ")->execute();
    }

    private function createTriggersWithoutRelatedId()
    {
        $this->getAdapter()->getConnection()->exec("
         CREATE TRIGGER TAU_DEL_PF_Warnings
         ON PF_Warnings
         AFTER DELETE
         AS
         BEGIN
             SET NOCOUNT ON;
             BEGIN TRY
                 INSERT INTO TAU_PF_Warnings
                 (idaviso, idempresa, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, tau_tipo)
                 SELECT
                     idaviso, idempresa, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, 1
                 FROM DELETED;
             END TRY
             BEGIN CATCH
             END CATCH
         END;
        ");
        $this->getAdapter()->getConnection()->exec("
        CREATE TRIGGER TAU_UPD_PF_Warnings
        ON PF_Warnings
        AFTER UPDATE
        AS
        BEGIN
            SET NOCOUNT ON;
            BEGIN TRY
                INSERT INTO TAU_PF_Warnings
                (idaviso, idempresa, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, tau_tipo)
                SELECT
                    idaviso, idempresa, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, 2
                FROM DELETED;
            END TRY
            BEGIN CATCH
            END CATCH
        END;
        ");
    }

    private function createTriggersWithRelatedId()
    {
        $this->getAdapter()->getConnection()->exec("
         CREATE TRIGGER TAU_DEL_PF_Warnings
         ON PF_Warnings
         AFTER DELETE
         AS
         BEGIN
             SET NOCOUNT ON;
             BEGIN TRY
                 INSERT INTO TAU_PF_Warnings
                 (idaviso, idempresa, idrelacionado, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, tau_tipo)
                 SELECT
                     idaviso, idempresa, idrelacionado, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, 1
                 FROM DELETED;
             END TRY
             BEGIN CATCH
             END CATCH
         END;
        ");
        $this->getAdapter()->getConnection()->exec("
        CREATE TRIGGER TAU_UPD_PF_Warnings
        ON PF_Warnings
        AFTER UPDATE
        AS
        BEGIN
            SET NOCOUNT ON;
            BEGIN TRY
                INSERT INTO TAU_PF_Warnings
                (idaviso, idempresa, idrelacionado, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, tau_tipo)
                SELECT
                    idaviso, idempresa, idrelacionado, ntipo, idcliente, naviso, lativo, ndiasaviso, idusucad, dcadas, idusu, datual, 2
                FROM DELETED;
            END TRY
            BEGIN CATCH
            END CATCH
        END;
        ");
    }

    private function createRelatedIdColumn()
    {
        if ($this->table($this->tableAviso)->hasColumn('idrelacionado') === false) {
            $this->table($this->tableAviso)->addColumn('idrelacionado', 'integer', [
                'null' => false,
                'default' => 0,
                'after' => 'ntipo',
            ])->save();
            if ($this->hasStatistic($this->tableAviso, 'idrelacionado') === false) {
                $this->createStatistic($this->tableAviso, 'idrelacionado');
            }
        }
    }

    private function dropRelatedIdColumn()
    {
        if ($this->table($this->tableAviso)->hasColumn('idrelacionado')) {
            if ($this->hasStatistic($this->tableAviso, 'idrelacionado')) {
                $this->dropStatistic($this->tableAviso, 'idrelacionado');
            }
            $this->table($this->tableAviso)->removeColumn('idrelacionado')->update();
        }
    }