Trigger exits on first failed insert and cant set xact_abort OFF in SQL Server 2000

Question

I have built a simple database to run on SQL Server 2005 and now need to get it working on SQL Server 2000. The aim is as follows:

• I receive a daily data feed containing ~5k records into a Staging table.
• When this insert is done a single record is then added to a table called TRIGGER_DATA.
• I have created a trigger ‘on insert’ on this table which then attempts to insert the data therein into a FACT_data table one record at a time.
• The FACT_data table is foreign keyed to many DIM tables which define the acceptable inputs the field can take.
• If any record violates a foreign key constraint the insert should fail and the record should instead be inserted into a Load_error table (which has no foreign key and all fields are Nullable).

The trigger has a main try-catch and i then use a second try-catch on the EXEC statement for the insert into the FACT_data table to direct failed records into the Load_error table.

Under Compatibility level 90 (ie SQL server 2005) I can set XACT_ABORT to OFF within a trigger and everything seems to work fine - the below code works. However under compatibility level 80 or less you cannot set this parameter from within a trigger and my code fails the first time it encounters an insert error.

My only thought of a workaround at present is to create an on insert trigger on the Stage_data table and each trigger then processes one row of data. I dont like this solution however as the data volume is quite large and may increase and it doesnt seem like a good idea to have 5000 triggers go off at the one time.

Can anyone provide or point me in the direction of a solution please?

--------- SQL SERVER 2005 code extract:

CREATE TRIGGER DataReceived

ON TRIGGER_DATA FOR INSERT AS BEGIN SET NOCOUNT ON;
DECLARE @strfields varchar(4000) DECLARE @sql1 VARCHAR(8000); DECLARE @sql2 VARCHAR(8000); DECLARE @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;

END

Many thanks

------------- EDIT: -----------------------------------------

I have tried to modify my code to work under sql server 2000 (ie replacing try-catch with @@error checks). However it aborts fails every time a FK violation occurs. Can anyone tell me what I am doing wrong?

ALTER TRIGGER DataReceived

ON TRIGGER FOR INSERT AS BEGIN SET NOCOUNT ON;
DECLARE @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

END