Insert Trigger - Foreign Key Violation - UnCommittable transaction - SQL Server 2005
-
I am facing an issue in Insert Trigger. 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
modified on Friday, September 24, 2010 5:31 AM