T-SQL Trigger - prevent duplicate, but not always

Question

I would like to avoid INSERTS and UPDATES which could duplicate field's value, but not always.

I have a varchar field Cat_Catalog. in table Catalog.

I can have two rows with Cat_Catalog's value "123" duplicated, but I cannot have duplicated field Cat_Catalog which starts with 'KAT' word (so I cannot have 2 rows with "KAT123" Cat_Catalog's value)

The following trigger i made doesn't work fine because field that's going to be updated starts with KAT trigger always raise error (variable @IfExist always return true - it is probably because of AFTER UPDATE,INSERT syntax).

I would like to avoid using INSTEAD OF syntax because updates are generated by some API which to i have no documentation and I'm not sure what to do in case when value doesn't starts with 'KAT'.

GO
/****** Object:  Trigger [dbo].[Catalog_InsertUpdateCatalog]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

--If first three characters are 'KAT' 
--then check for duplicate and raiseerror

ALTER TRIGGER [dbo].[Catalog_InsertUpdateCatalog] ON [dbo].[Catalog]
FOR UPDATE,INSERT
AS
set nocount on;

DECLARE @CatalogInsert varchar(50)
DECLARE @IfKat varchar(10) = 'FALSE'
DECLARE @IfExist varchar(10) = 'FALSE'

SELECT @CatalogInsert = Cat_Catalog
FROM inserted

--Does It starts with 'KAT' ?
IF (@CatalogInsert like 'KAT%')
BEGIN
    SET @IfKat = 'TRUE'
END

--Check for Duplicate
IF EXISTS(
            Select * from Test.dbo.Catalog t
            where t.Cat_Catalog = @CatalogInsert
        )
        BEGIN
            SET @IfExist = 'TRUE'
        END

IF ( @IfExist = 'TRUE' and @IfKat = 'TRUE' )
    BEGIN
        RAISERROR ('Catalog allready exists: %s , ISKAT:%s , EXIST:%s', 16, 1, @CatalogInsert, @IfKat, @IfExist);
    END 

`

The problem is that I don't know how can I check the current value to be updated allready exists in Catalog table (check must be done before update).

Answer 1

Try these two triggers:

CREATE TRIGGER [MyCatalog_InsertCatalog] ON [dbo].[MyCatalog]
FOR INSERT
AS

IF EXISTS(
    SELECT I.* 
    FROM Inserted I 
    INNER JOIN MyCatalog M 
        ON M.Cat_Catalog = I.Cat_Catalog 
    WHERE I.Cat_Catalog  LIKE 'KAT%' )
BEGIN
    RAISERROR ('Insert Catalog already exists:  ', 16, 1);
    ROLLBACK TRANSACTION;
END

GO

CREATE TRIGGER [MyCatalog_UpdateCatalog] ON [dbo].[MyCatalog]
FOR UPDATE
AS

IF  EXISTS(
    SELECT I.* 
    FROM Inserted I 
    INNER JOIN MyCatalog M 
        ON M.Cat_Catalog = I.Cat_Catalog 
    INNER JOIN deleted d 
        ON d.Cat_Id = i.Cat_Id 
    WHERE I.Cat_Catalog LIKE 'KAT%' AND d.Cat_Catalog <> i.Cat_Catalog)
BEGIN

    RAISERROR ('Update Catalog already exists: ', 16, 1);
    ROLLBACK TRANSACTION;
END

EDIT

You could combine both triggers into one:

CREATE TRIGGER [MyCatalog_InsertUpdateCatalog] ON [dbo].[MyCatalog]
FOR INSERT, UPDATE
AS

IF  EXISTS(
    SELECT I.* 
    FROM Inserted I 
    INNER JOIN MyCatalog M 
        ON M.Cat_Catalog = I.Cat_Catalog 
    INNER JOIN deleted d 
        ON d.Cat_Id = i.Cat_Id 
    WHERE I.Cat_Catalog LIKE 'KAT%' AND d.Cat_Catalog <> i.Cat_Catalog)
BEGIN

    RAISERROR ('Update Catalog already exists: ', 16, 1);
    ROLLBACK TRANSACTION;
END
ELSE BEGIN
   IF EXISTS(
       SELECT I.* 
       FROM Inserted I 
       INNER JOIN MyCatalog M 
           ON M.Cat_Catalog = I.Cat_Catalog 
       WHERE I.Cat_Catalog  LIKE 'KAT%' )
   BEGIN
       RAISERROR ('Insert Catalog already exists:  ', 16, 1);
       ROLLBACK TRANSACTION;
   END
END

Answer 2

Please, try this new version:

    ALTER TRIGGER [dbo].[Catalog_InsertUpdateCatalog] ON [dbo].[Catalog]
    FOR UPDATE,INSERT 
    AS BEGIN
      set nocount on;
      --Check for Duplicate
      IF EXISTS(
         Select 1 
         From (
            -- Updated
            SELECT 
              COUNT(*) OVER (PARTITION BY Cat_Catalog) Cnt, 
              Cat_Catalog  
            FROM dbo.Catalog 
            WHERE 
               Cat_Catalog LIKE 'KAT%'
         ) t
         Join inserted i ON t.Cat_Catalog = i.Cat_Catalog AND Cnt > 1
      )
      BEGIN
         RAISERROR ('Catalog allready exists!', 16, 1);
         ROLLBACK TRANSACTION;
      END

    END 

This trigger check the existence of rows with the same [Cat_Catalog] field as in the inserted (or updated) rows. If duplicated rows exists and [Cat_Catalog] starts with 'KAT' trigger RAISE error + rollback transaction.

UPDATED: I change trigger. It should now work correctly (i test it). Trigger FOR UPDATE, INSERT fires after changes take place in table. So we need check duplicate rows in the table.
I do it throught COUNT(*) OVER(PARTITION BY Cat_Catalog) but you may check it in a more familiar way:

    SELECT 
        COUNT(*) Cnt, 
        Cat_Catalog  
    FROM 
        dbo._Catalog 
    WHERE 
        Cat_Catalog LIKE 'KAT%'
    GROUP BY
        Cat_Catalog

Answer 3

Using CHECK constraint instead of a trigger would be a better solution, since triggers are executed much later in the transaction and eventual rollback could be expensive. You can define check constraint as follows:

CREATE FUNCTION IsDuplicate(@col varchar(50))
RETURNS BIT
AS 
BEGIN
    IF CHARINDEX('KAT', @col) = 1 AND (SELECT COUNT(*) FROM [Catalog] WHERE [Cat_Catalog] = @col) > 1
        return 1;

    return 0;
END;
GO

ALTER TABLE [Catalog]
    ADD CONSTRAINT chkForDuplicates CHECK (dbo.IsDuplicate([Cat_Catalog]) = 0)
GO

Have in mind that if you already have duplicate "KATxxx" values in the table you'll have to either delete them or create the constraint with NOCHECK clause.

Answer 4

Use Instead OF Trigger instead After Trigger.

If your problem with Instead OF Triggers, then you need to use NOT Eqals operator to avoid such wrong indication to passed. I supposed to say

      DECLARE @CATALOG_PK BIGINT;
      SELECT @CATALOG_PK = CATALOG_PK FROM INSERTED

  --Check for Duplicate
   IF EXISTS(
        Select * from Test.dbo.Catalog t
        where t.Cat_Catalog = @CatalogInsert
        AND CATALOG_PK <>@CATELOG_PK LIKE t.CATALOG LIKE 'KAT%' 
          )
    BEGIN
        SET @IfExist = 'TRUE'
    END

However this will fail in-case of multiple UPDATES or INSERTS done.