數(shù)據(jù)操作技巧: 禁用約束和觸發(fā)器

CREATE PROCEDURE pr_Disable_Triggers_v2 
    @disable BIT = 1
AS 
    DECLARE
        @sql VARCHAR(500),
        @tableName VARCHAR(128),
        @tableSchema VARCHAR(128)

    -- List of all tables
    DECLARE triggerCursor CURSOR
        FOR
    SELECT
        t.TABLE_NAME AS TableName,
        t.TABLE_SCHEMA AS TableSchema
    FROM
        INFORMATION_SCHEMA.TABLES t
    ORDER BY
        t.TABLE_NAME,
        t.TABLE_SCHEMA 

    OPEN triggerCursor

    FETCH NEXT FROM triggerCursor 
    INTO @tableName, @tableSchema

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @disable = 1 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] DISABLE TRIGGER ALL’ 
            ELSE 
                SET @sql = ‘ALTER TABLE ‘ + @tableSchema 
                    + ‘.[‘ + @tableName + ‘] ENABLE TRIGGER ALL’ 

            PRINT ‘Executing Statement - ‘ + @sql

            EXECUTE ( @sql )
            FETCH NEXT FROM triggerCursor
            INTO @tableName, @tableSchema
        END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor

CREATE PROCEDURE pr_Disable_Triggers 
@disable BIT = 1
AS 
    DECLARE
        @sql VARCHAR(500),
        @tableName VARCHAR(128),
        @triggerName VARCHAR(128),
        @tableSchema VARCHAR(128)

    -- List of all triggers and tables that exist on them
    DECLARE triggerCursor CURSOR
        FOR
    SELECT
        so_tr.name AS TriggerName,
        so_tbl.name AS TableName,
        t.TABLE_SCHEMA AS TableSchema
    FROM
        sysobjects so_tr
    INNER JOIN sysobjects so_tbl ON so_tr.parent_obj = so_tbl.id
    INNER JOIN INFORMATION_SCHEMA.TABLES t 
    ON 
        t.TABLE_NAME = so_tbl.name
    WHERE
        so_tr.type = ‘TR’
    ORDER BY
        so_tbl.name ASC,
        so_tr.name ASC

    OPEN triggerCursor

    FETCH NEXT FROM triggerCursor 
    INTO @triggerName, @tableName, @tableSchema

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @disable = 1 
                SET @sql = ‘DISABLE TRIGGER [‘ 
                    + @triggerName + ‘] ON ‘ 
                    + @tableSchema + ‘.[‘ + @tableName + ‘]’
            ELSE 
                SET @sql = ‘ENABLE TRIGGER [‘ 
                    + @triggerName + ‘] ON ‘ 
                    + @tableSchema + ‘.[‘ + @tableName + ‘]’

            PRINT ‘Executing Statement - ‘ + @sql
            EXECUTE ( @sql )
            FETCH NEXT FROM triggerCursor 
            INTO @triggerName, @tableName,  @tableSchema
        END

    CLOSE triggerCursor
    DEALLOCATE triggerCursor

CREATE PROCEDURE pr_Disable_Foreign_Keys
    @disable BIT = 1
AS
    DECLARE
        @sql VARCHAR(500),
        @tableName VARCHAR(128),
        @foreignKeyName VARCHAR(128)

    -- A list of all foreign keys and table names
    DECLARE foreignKeyCursor CURSOR
    FOR SELECT
        ref.constraint_name AS FK_Name,
        fk.table_name AS FK_Table
    FROM
        INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
        INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk 
    ON ref.constraint_name = fk.constraint_name
    ORDER BY
        fk.table_name,
        ref.constraint_name 

    OPEN foreignKeyCursor

    FETCH NEXT FROM foreignKeyCursor 
    INTO @foreignKeyName, @tableName

    WHILE ( @@FETCH_STATUS = 0 )
        BEGIN
            IF @disable = 1
                SET @sql = ‘ALTER TABLE [‘ 
                    + @tableName + ‘] NOCHECK CONSTRAINT [‘ 
                    + @foreignKeyName + ‘]’
            ELSE
                SET @sql = ‘ALTER TABLE [‘ 
                    + @tableName + ‘] CHECK CONSTRAINT [‘ 
                    + @foreignKeyName + ‘]’

        PRINT ‘Executing Statement - ‘ + @sql

        EXECUTE(@sql)
        FETCH NEXT FROM foreignKeyCursor 
        INTO @foreignKeyName, @tableName
    END

    CLOSE foreignKeyCursor
    DEALLOCATE foreignKeyCursor