SQL Server Trigger – How to Rename Target Table

sql serversql-server-2008-r2trigger

We have a table that serves as an audit trail table for an app. Let's call it ProductAudit, and let's call the data table for this audit table Products. ProductsAudit got polluted with about 150 million rows due to a bug. There are about 2 million valid rows that need to remain.

So for cleanup, I want to:

  • create a new table, ProductAudit_TEMP
  • copy over the 2 million good rows
  • drop table ProductAudit
  • rename ProductAudit_TEMP to ProductAudit.

This will be much faster than deleting the 150 million invalid rows – even in batches.

My question is, table Products has INSERT, UPDATE, and DELETE triggers, which write into table ProductAudit. Will my plan break the triggers? I'll have all users out of the system, and even disable the triggers for safety when performing the cleanup.

EDIT:

Just to clarify, ProductAudit does not have triggers, Products does. ProductAudit is being populated by the INSERT, UPDATE, and DELETE triggers that belong to table Products.

Best Answer

As @AaronBertrand pointed out in his comment, objects are attached to other objects by object_id, not by object name. Having said that, there is a way to move data from one table to another without the need to use sp_rename.

Since example code is the cleanest way to show how something works, I've created the following test-bed code to illustrate how to use ALTER TABLE ... SWITCH syntax to enable data migration.

First, we'll create an "existing" Product and ProductAudit table, with a trigger on the Product table, that inserts rows into the ProductAudit table:

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

Here, we'll insert 100,000 rows into the Product table:

;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;

Results of the SELECT queries above:

ProductCount
------------
100000

ProductAuditCount
-----------------
100000

This will create a "temporary" table to hold the rows we want to retain:

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
);

This code "migrates" the data from the existing table into the new table, retaining the triggers present on the original table, without recreating them. The ALTER TABLE ... SWITCH syntax is a meta-data-only operation that is extremely quick, making this a great way to migrate data. The code below sets the transaction isolation level to lock the tables involved to prevent other processes inserting or updating data in any of the involved tables. 

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;

Results:

ProductAuditCount
-----------------
20000

Here, we'll insert another 10 rows into the dbo.Product table to prove the trigger still works:

;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;

Results:

ProductAuditCount
-----------------
20010
Related Question