1

I need a trigger that will prevent from updating price value if existing value is higher than inserted. 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 update 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

2

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

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.