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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy