Триггер завершается при первой неудачной вставке и не может отключить xact_abort в SQL Server 2000

Question

Я создал простую базу данных для работы на SQL Server 2005, и теперь мне нужно, чтобы она работала на SQL Server 2000. Цель заключается в следующем:

• Я получаю ежедневный поток данных, содержащий ~5 тыс. Записей в промежуточную таблицу.
• Когда эта вставка завершена, в таблицу добавляется одна запись, которая называется TRIGGER_DATA.
• Я создал триггер "при вставке" для этой таблицы, который затем пытается вставить данные в таблицу FACT_data по одной записи за раз.
• Таблица FACT_data является внешним ключом для многих таблиц DIM, которые определяют допустимые входные данные, которые может принимать поле.
• Если какая-либо запись нарушает ограничение внешнего ключа, вставка должна завершиться неудачей, и вместо этого запись должна быть вставлена ​​в таблицу Load_error (которая не имеет внешнего ключа, и все поля имеют значение Nullable).

Триггер имеет основной try-catch, а затем я использую второй try-catch в операторе EXEC для вставки в таблицу FACT_data, чтобы направить ошибочные записи в таблицу Load_error.

На уровне совместимости 90 (т. Е. SQL Server 2005) я могу установить для XACT_ABORT значение OFF в триггере, и, кажется, все работает нормально - приведенный ниже код работает. Однако на уровне совместимости 80 или ниже вы не можете установить этот параметр из триггера, и мой код завершается ошибкой при первом обнаружении ошибки вставки.

В настоящее время я думаю только об обходном пути - создать триггер при вставке в таблицу Stage_data, и каждый триггер затем обрабатывает одну строку данных. Однако мне не нравится это решение, так как объем данных достаточно велик и может увеличиться, и не очень хорошая идея, чтобы 5000 триггеров срабатывали одновременно.

Кто-нибудь может предоставить или указать мне направление решения, пожалуйста?

--------- Извлечение кода SQL SERVER 2005:

CREATE TRIGGER DataReceived

ON TRIGGER_DATA FOR INSERT AS BEGIN НАЧАТЬ УСТАНОВЛЕНО;
Объявить @strfields varchar(4000) объявить @sql1 VARCHAR(8000); ОБЪЯВИТЬ @sql2 VARCHAR(8000); ОБЪЯВИТЬ @row_ID varchar(10)

CREATE TABLE #new_ids (sale_id INT )

SET @strfields = 'field1, field2, field3'

-- Insert into FACT_DATA data present in STAGE_DATA
INSERT INTO Log_Data (category, msg, tstamp) VALUES ('General', 'Trigger: Data Received', getdate());

BEGIN TRY       
    DECLARE Stage_Cursor CURSOR FAST_FORWARD
    FOR
    SELECT cast(ID as varchar(10)) FROM STAGE_DATA

    OPEN Stage_Cursor 
    FETCH NEXT FROM Stage_Cursor INTO @row_ID

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @sql1 = ' INSERT FACT_DATA (sale_date_id, '+@strfields+') 
                    OUTPUT INSERTED.sale_id INTO #new_ids'
        SET @sql2 =  ' SELECT c.sale_date_id,a.* FROM (SELECT '+@strfields+' from STAGE_DATA where ID =  '+ @row_id + ') AS a
                 JOIN   
                (SELECT sale_date_id,s_date from DIM_calendar) AS c
                on a.STORED_DT = c.s_date;'
        BEGIN TRY   
            SET XACT_ABORT OFF          
            EXEC(@sql1 + @sql2)
        END TRY
        BEGIN CATCH
            --catch records that did not load 
            SET @sql1 = 'INSERT INTO Load_error ('+@strfields+') ' 
            SET @sql2 = 'SELECT '+@strfields+' from STAGE_DATA where ID =  '+ @row_id
            EXEC(@sql1 + @sql2)
        END CATCH   
        FETCH NEXT FROM Stage_Cursor INTO @row_ID
    END
    CLOSE Stage_Cursor
    DEALLOCATE Stage_Cursor

    --re-enable auto abort
    SET XACT_ABORT ON;

    --clear tables
    TRUNCATE TABLE STAGE_DATA;
    TRUNCATE TABLE TRIGGER_DATA;    

END TRY
BEGIN CATCH
    declare @error int, @message varchar(4000), @xstate int;
   select @error = ERROR_NUMBER(), @message = ERROR_MESSAGE(), @xstate = XACT_STATE();
    if @xstate = -1
        rollback;
    if @xstate = 1 and @@trancount = 0
        rollback;
    if @xstate = 1 and @@trancount > 0
        rollback ;

    INSERT INTO Log_Data (category, msg, tstamp) VALUES ('General', '***FAILED. Trigger: FACT_DATA update', getdate());
    raiserror ('DataReceived: %d: %s', 16, 1, @error, @message) ;

   return;
END CATCH;

КОНЕЦ

Большое спасибо

------------- РЕДАКТИРОВАТЬ: -----------------------------------------

Я пытался изменить свой код для работы под SQL Server 2000 (то есть замена try-catch с проверками ошибок @@). Однако это прерывает неудачу каждый раз, когда происходит нарушение FK. Может кто-нибудь сказать мне, что я делаю не так?

ALTER TRIGGER DataReceived

ON TRIGGER ДЛЯ ВСТАВКИ КАК НАЧАТЬ УСТАНОВИТЬ;
ОБЪЯВИТЬ @row_ID INT

-- Insert into FACT_POS data present in STAGE_DATA
INSERT INTO Log_Data (category, msg, tstamp) VALUES ('General', 'Trigger: Data Received', getdate());

DECLARE @err int     
--select 1/0; --generate error
DECLARE Stage_Cursor CURSOR FAST_FORWARD
FOR
SELECT ID  FROM STAGE_DATA

OPEN Stage_Cursor 
FETCH NEXT FROM Stage_Cursor INTO @row_ID

WHILE @@FETCH_STATUS = 0
BEGIN
    --load data
    INSERT FACT_DATA (sale_date_id,field1,field2,field3)
    SELECT c.sale_date_id,a.* FROM (SELECT field1,field2,field3 
    from STAGE_DATA where ID = @row_ID)  AS a
     JOIN   
    (SELECT sale_date_id,s_date from DIM_calendar) AS c
    on a.STORED_DT = c.s_date; 

    --check error code
    SELECT @err=@@error 
    If @err <>0 
    BEGIN
        --TO DO: catch records that did not load 
        --raiserror - this should prevent the trigger from aborting the batch
        RAISERROR('Error happened', 16, 1)
    END
    FETCH NEXT FROM Stage_Cursor INTO @row_ID
END
CLOSE Stage_Cursor
DEALLOCATE Stage_Cursor

КОНЕЦ