Insert Trigger - Foreign Key Violation - UnCommittable transaction

  • I am facing an issue in Insert Trigger in SQL Server 2005.

    I have 2 tables. Whenever I insert into Table 1, I have written a trigger to insert into Table 2. There is a stored procedure to insert in to Table 1 and there is an After Insert Trigger to insert into Table 2. The table 2 has one primary key and one foreign key.

    When there is a Primary Key constraint violation (Or Unique Key), I am able to catch the error in the stored procedure and I am able to display the same to the user.

    But if there is a foreign key constraint violation, I am not able to catch the error "Foreign Key Violation". Instead I am getting an error called "UnCommittable transaction".

    Is there any way to catch this Foreign Key Violation error and display it to the user?

    Note: I have a "Try... Catch" block in the stored procedure. But I don't have it in the Insert Trigger.

    Thanks and Regards,

    Peri

  • How about this:

    use tempdb

    go

    drop table T3

    go

    drop table T2;

    go

    drop table T1;

    go

    drop proc usp_new_T1

    go

    Create table T1

    ( col1 int identity(1, 1)

    not null

    primary key

    , tsinsert datetime not null

    default getdate()

    ) ;

    Create table T2

    ( col2 int not null

    primary key

    , tsinsert datetime not null

    ) ;

    go

    create trigger tr_I on T1

    AFTER INSERT

    AS

    set nocount on

    insert into T2 (col2 , tsinsert )

    select col1, tsinsert from inserted

    go

    create proc usp_new_T1

    as

    begin

    set nocount on

    begin try

    insert into T1

    default values ;

    end try

    begin catch

    SELECT ERROR_NUMBER()

    , ERROR_SEVERITY()

    , ERROR_STATE()

    , ERROR_PROCEDURE()

    , ERROR_LINE()

    , ERROR_MESSAGE()

    IF @@TRANCOUNT > 0

    ROLLBACK TRANSACTION ;

    end catch

    end

    go

    insert into T1 default values ;

    insert into T1 default values ;

    insert into T1 default values ;

    select *

    from t2

    go

    -- force the next insert as a pk violation

    insert into t2 values ( 4, '2010-04-01' )

    go

    exec usp_new_T1

    go

    Create table T3 (col3 int not null primary key, fk int not null, tsinsert datetime not null );

    go

    ALTER TABLE dbo.T3 ADD CONSTRAINT

    FK_T3_T1 FOREIGN KEY

    (

    fk

    ) REFERENCES dbo.T1

    (

    col1

    ) ON UPDATE NO ACTION

    ON DELETE NO ACTION

    go

    alter trigger tr_I on T1

    AFTER INSERT

    AS

    set nocount on

    insert into T3 (col3, fk , tsinsert )

    select col1, col1 - 2, tsinsert from inserted

    go

    exec usp_new_T1 ;

    exec usp_new_T1 ;

    select *

    from t1 ;

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply