Renomeando a tabela de destino de um gatilho

Como @AaronBertrand apontou em seu comentário, os objetos são anexados a outros objetos por object_id , não por object name . Dito isto, existe uma maneira de mover dados de uma tabela para outra sem a necessidade de usar sp_rename.

Como o código de exemplo é a maneira mais limpa de mostrar como algo funciona, criei o código de teste a seguir para ilustrar como usar ALTER TABLE ... SWITCHa sintaxe para habilitar a migração de dados.

Primeiro, criaremos uma tabela Producte "existente" ProductAudit, com um gatilho na Producttabela, que insere linhas na ProductAudittabela:

USE tempdb;

IF OBJECT_ID('dbo.Product') IS NOT NULL
DROP TABLE dbo.Product;
CREATE TABLE dbo.Product
(
    ProductKey int NOT NULL
        CONSTRAINT PK_Product
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , SomeData varchar(4000) NOT NULL
    , TriggerUpdated bit NOT NULL
        CONSTRAINT df_Product_TriggerUpdated
        DEFAULT (0)
);
GO
IF OBJECT_ID('dbo.ProductAudit') IS NOT NULL
DROP TABLE dbo.ProductAudit;
CREATE TABLE dbo.ProductAudit
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAudit
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);
GO

CREATE TRIGGER dbo.ProductAuditInsertTrigger
ON dbo.Product
AFTER INSERT 
AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.ProductAudit (ProductKey, SomeData)
    SELECT i.ProductKey
        , i.SomeData
    FROM inserted i
    UPDATE dbo.Product
    SET TriggerUpdated = 1
    FROM dbo.Product p
        INNER JOIN inserted i ON p.ProductKey = i.ProductKey;
END
GO

Aqui, vamos inserir 100.000 linhas na Producttabela:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1
    CROSS APPLY src s2
    CROSS APPLY src s3
    CROSS APPLY src s4
    CROSS APPLY src s5;

SELECT ProductCount = COUNT(*)
FROM dbo.Product pa
WHERE pa.TriggerUpdated = 1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Resultados das SELECTconsultas acima:

ProdutoContagem
------------
100.000

ProductAuditCount
-----------------
100.000

Isso criará uma tabela "temporária" para conter as linhas que queremos reter:

IF OBJECT_ID('dbo.ProductAuditNew') IS NOT NULL
DROP TABLE dbo.ProductAuditNew;
CREATE TABLE dbo.ProductAuditNew
(
    ProductAuditKey int NOT NULL
        CONSTRAINT PK_ProductAuditNew
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ProductKey int NOT NULL
    , SomeData varchar(4000) NOT NULL
);

Este código "migra" os dados da tabela existente para a nova tabela, mantendo os triggers presentes na tabela original, sem recriá-los. A ALTER TABLE ... SWITCHsintaxe é uma operação somente de metadados que é extremamente rápida, tornando essa uma ótima maneira de migrar dados. O código abaixo define o nível de isolamento da transação para bloquear as tabelas envolvidas para impedir que outros processos insiram ou atualizem dados em qualquer uma das tabelas envolvidas.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; --lock all row-ranges affected by 
                                              --the migration
DECLARE @msg varchar(1000);
BEGIN TRANSACTION
BEGIN TRY
    --disable the trigger temporarily
    DISABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    --copy the rows we want to retain from the old table to the new table
    INSERT INTO dbo.ProductAuditNew (ProductKey, SomeData)
    SELECT pa.ProductKey
        , pa.SomeData
    FROM dbo.ProductAudit pa
    WHERE pa.ProductAuditKey % 5 = 0; --retain 20% of the rows
    --truncate the "old" table
    TRUNCATE TABLE dbo.ProductAudit;
    --switch the single partition belonging to the "new" table
    --into the "old" table
    ALTER TABLE dbo.ProductAuditNew 
    SWITCH TO dbo.ProductAudit;
    --remove the "new" table, which is actually empty now
    DROP TABLE dbo.ProductAuditNew;
    --re-seed the table's identity object; only necessary if an IDENTITY column is present
    DBCC CHECKIDENT('dbo.ProductAudit', RESEED);
    --re-enable the trigger
    ENABLE TRIGGER dbo.ProductAuditInsertTrigger ON dbo.Product;
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    SET @msg = ERROR_MESSAGE();
    PRINT (@msg);
    ROLLBACK TRANSACTION
END CATCH
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; --return to default mode


SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Resultados:

ProductAuditCount
-----------------
20.000

Aqui, inseriremos outras 10 linhas na dbo.Producttabela para provar que o gatilho ainda funciona:

;WITH src AS (
    SELECT *
    FROM (VALUES(0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v(num)
)
INSERT INTO dbo.Product (SomeData)
SELECT CONVERT(varchar(4000), CRYPT_GEN_RANDOM(4000))
FROM src s1;

SELECT ProductAuditCount = COUNT(*)
FROM dbo.ProductAudit pa;

Resultados:

ProductAuditCount
-----------------
20010

Se o acionador estiver na tabela Produtos e estiver gravando na tabela ProductAudit, tudo bem, desde que exista uma tabela chamada ProductAudit no momento em que o acionador for executado.

O truque é garantir que você não tenha linhas de auditoria sendo gravadas pelo gatilho depois de achar que já moveu todas as linhas boas de ProductAudit para ProductAudit_TEMP. A maneira simples de garantir que isso não aconteça seria definir o banco de dados para algo como o modo RESTRICTED_USER e garantir que ninguém mais possa se conectar. Se você precisar fazer isso sem desativar completamente o aplicativo, talvez seja necessário fazer tudo em uma transação envolvendo algumas dicas TABLOCK, XLOCK, HOLDLOCK ou modificar temporariamente o gatilho para gravar diretamente em ProductAudit_TEMP.