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
    ======================

  • quoteAfter 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