2

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

1
  • Przemek: You should avoid returning a result set (i.e. the SELECT NULL) from a trigger. If you need a statement that effectively does "nothing", then try: DECLARE @DoNothing INT;. – Solomon Rutzky Oct 29 '20 at 19:39
3

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
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.