Hi,

I have a trigger need to twist to prevent updating duplicate values.  Business rules and desire result are below.

Business rule: 1.  Duplicate values in drawerno, DrawerStoreNo, DrawerRegisterNo are NOT allowed, but NULL is O.K
               2.  Update these 3 columns to existing values are not allowed.

The problem I have now is how can I change the trigger to check for if UPDATE and the values already existing.
This could be simple to add an UPDATE in a trigger but some reasons I got stuck.
Please see the desire result below.

Thank you in advance.

-----------------------------------------------------------------------------------
IF OBJECT_ID('Phrmcst', 'u') IS NOT NULL
  DROP TABLE dbo.Phrmcst
GO
CREATE TABLE dbo.Phrmcst
(
   PKey             INT IDENTITY (1, 1) NOT NULL PRIMARY KEY,
   drawerno         INT       NULL,
   DrawerStoreNo    INT       NULL,
   DrawerRegisterNo INT       NULL,
   UserId           VARCHAR(10) NULL
)
GO

INSERT Phrmcst (drawerno, DrawerStoreNo, DrawerRegisterNo ) 
VALUES (1, 1, 1), ( 2, 3, NULL), (NULL, NULL, NULL), (NULL, NULL, NULL);
GO

   SELECT *
     FROM Phrmcst
   GO
   
PKey        drawerno    DrawerStoreNo DrawerRegisterNo UserId
----------- ----------- ------------- ---------------- ----------
1           1           1             1                NULL
2           2           3             NULL             NULL
3           NULL        NULL          NULL             NULL
4           NULL        NULL          NULL             NULL

--------------------------------------------------------------------------
-- Trigger.

IF NULLIF (OBJECT_ID('dbo.rg_ins_Phrmcst'), 0) > 0
  DROP TRIGGER dbo.trg_ins_Phrmcst;
GO

CREATE TRIGGER dbo.trg_ins_Phrmcst
ON dbo.Phrmcst
FOR INSERT
AS
SET NOCOUNT ON;

BEGIN

    IF EXISTS (  SELECT 1  
                   FROM dbo.Phrmcst AS a
                   JOIN INSERTED AS i  
                     ON a.drawerno = i.drawerno
                    AND a.DrawerStoreNo = i.DrawerStoreNo  
                    AND a.DrawerRegisterNo = i.DrawerRegisterNo
                 HAVING COUNT(1) > 1 
               )
     BEGIN
       RAISERROR ('Cannot enter duplicate values.  Please check again.', -1, 1) WITH NOWAIT
       PRINT ' '
       ROLLBACK TRANSACTION
       --PRINT 'Transaction is Rolling Back.'
       --PRINT 'Trancount count: ' + CAST(@@Trancount AS CHAR(2)) 
       RETURN

     END
END
GO

--------------------------------------------------------------------------

-- Testing Update...
DECLARE @drawerno INT = 1
DECLARE @DrawerStoreNo INT = 1
DECLARE @DrawerRegisterNo INT = 1


    -- How can I get an error, if the values already exist.  Something like Values already exists.
    UPDATE Phrmcst
       SET drawerno = @drawerno,
           DrawerStoreNo = @DrawerStoreNo,
           DrawerRegisterNo = @DrawerRegisterNo
     WHERE PKey = 4;
     go


    SELECT *
      FROM Phrmcst
    GO

  -- DELETE T1 WHERE PKey > 3;
  
  EXECute sp_depends Phrmcst
  
  -- DROP TRIGGER trg_test1
  
DECLARE @drawerno INT = 1
DECLARE @DrawerStoreNo INT = 1
DECLARE @DrawerRegisterNo INT = 1

DECLARE @drawerno INT
DECLARE @DrawerStoreNo INT
DECLARE @DrawerRegisterNo INT

--SELECT @drawerno
--SELECT @DrawerStoreNo
--SELECT @DrawerRegisterNo

    INSERT Phrmcst ( drawerno, DrawerStoreNo, DrawerRegisterNo )
    SELECT @drawerno, @DrawerStoreNo, @DrawerRegisterNo
    GO