September 24, 2010 at 2:53 am
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
September 24, 2010 at 4:56 am
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