Trigger and custom error message question
-
I have a table witch I have to use a trigger to enforce som business rules.
If you se the sample code, I can not get my insert statement to return <Print 'Error occured while inserting 1'>
Can somebody help me to understand what I am doing wrong
(!! This i sample code, and I know that its better to use constraints here, but this is an example)
/* Create table */
if exists (select * from sysobjects where id = object_id(N'[dbo].[tTest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tTest]
GO
CREATE TABLE [dbo].[tTest] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[Name] [varchar] (50) NULL
) ON [PRIMARY]
GO
/* Add error message */
EXEC sp_addmessage 56000,16, N'Not possible to add name, duplicates will bee created','us_english' , False,'REPLACE'
go
/* Create trigger*/
CREATE TRIGGER tr_tTest_RI ON tTest
FOR UPDATE, INSERT
AS
DECLARE @iCount int
SELECT @iCount = -1
DECLARE @InsName varchar(50)
Select @InsName = inserted.Name from inserted
Select @iCount = Count(tTest.ID) from tTest where tTest.Name = @InsName
IF @iCount > 1
BEGIN
RAISERROR( 56000 , 1 , 1) WITH SETERROR
ROLLBACK TRANSACTION
END
go
/* Insert values*/
-- This vil be valid
Insert into tTest (tTest.name) Values ('insert 1')
If @@Error <> 0
Begin
Print 'Error occured while inserting 1'
End
go
-- This vil not be valid
Insert into tTest (tTest.name) Values ('insert 1')
If @@Error <> 0
Begin
Print 'Error occured while inserting 1'
End
go
-
The problem is the ROLLBACK in the trigger. It will stop execution of sql and therefore your test of @@ERROR wil not be performed.
If you remove the ROLLBACK you will see your message(s) but the record will still be inserted.
If you put the ROLLBACK at the same level as the insert then you should get the required results.
BEGIN TRANSACTION
Insert into tTest (tTest.name) Values ('insert 1')
If @@ERROR <> 0
Begin
ROLLBACK TRANSACTION
Print 'Error occured while inserting 1'
End
Else
Begin
COMMIT TRANSACTION
End
Far away is close at hand in the images of elsewhere.
Anon. -
Tnx David.
I will try it
Percu
-
F @iCount > 1
BEGIN
RAISERROR( 56000 , 1 , 1) WITH SETERROR
ROLLBACK TRANSACTION
END
go
should this not be
IF @iCount > 0
BEGIN
RAISERROR( 56000 , 1 , 1) WITH SETERROR
ROLLBACK TRANSACTION
END
go
After the first insert iCount is equal to one and not greater then one ????
Nigel Moore
====================== -
After the first insert iCount is equal to one and not greater then one ???? True but the trigger is fired after the record is inserted, therefore > 0 test will stop any insert, even the first.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply