Trigger to prevent update if existing value is higher than inserted value

Question

I need a trigger that will prevent from updating the price if the existing value is higher than the inserted value. If such operation transaction appears, the trigger should prevent value update but let transaction end (i.e. not roll it back). The one below doesn't work -- it doesn't let the update happen even if inserted value is higher than existing.

CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT *
                                 FROM deleted d
                                 JOIN inserted i
                                     ON d.PRC_PrcId = i.PRC_PrcId
                                  WHERE d.PRC_Value > i.PRC_Value)
SELECT NULL

SET NOCOUNT OFF

Regards, Przemek

Answer 1

You are using INSTEAD OF UPDATE, this means that each update will end in the trigger.

You can add an ELSE statement to do the update if the first IF is not true.

CREATE TRIGGER [dbo].[Prices_InsteadUpdate_Trigger]
ON [dbo].[Prices]
INSTEAD OF UPDATE
AS

SET NOCOUNT ON

IF UPDATE(PRC_Value) AND EXISTS (SELECT * FROM deleted d JOIN inserted i ON d.PRC_PrcId=i.PRC_PrcId WHERE d.PRC_Value>i.PRC_Value)
SELECT NULL
ELSE 
UPDATE P
SET PRC_Value = i.PRC_Value
FROM dbo.Prices P
INNER JOIN inserted i
ON i.PRC_PrcId = P.PRC_PrcId;

SET NOCOUNT OFF;

Testing

CREATE TABLE dbo.Prices(PRC_PrcId INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
                        PRC_Value numeric(5,2));


INSERT INTO dbo.Prices(PRC_Value)
VALUES(1.2)

GO

Lower Price

UPDATE dbo.Prices
SET PRC_Value = 0.5;

Result

(No column name)
NULL

Higher price

UPDATE dbo.Prices
SET PRC_Value = 1.5;

Result

PRC_PrcId   PRC_Value
1   1.50