Check constraints in Ms Sql Sever 2005

Question

Hi ,

I am trying to add a check constraint which verity if after an update the new value (which was inserted) is greater than old values which is already stored in table.

For example i have a "price" column which already stores value 100, if the update comes with 101 the is ok, if 99 comes then my constraint should reject the update process. Can this behavior be achieved using check constraints or should i try to use triggers or functions ?

Please advice me regarding this...

Thanks, Mircea

Answer 1

Check constraints can't access the previous value of the column. You would need to use a trigger for this.

An example of such a trigger would be

CREATE TRIGGER DisallowPriceDecrease
ON Products
AFTER UPDATE
AS
IF NOT UPDATE(price) 
   RETURN

IF EXISTS(SELECT * FROM inserted i 
                     JOIN deleted d 
                     ON i.primarykey = d.primarykey 
                      AND i.price< d.price)
BEGIN
    ROLLBACK TRANSACTION
    RAISERROR('Prices may not be decreased', 16, 1)
END

Answer 2

Triggers start as a quick fix, and end with a maintenance nightmare. The two big problems with triggers are:

• It's hard to see when a trigger is called. You can easily write an update statement without being aware that a trigger will run.
• When triggers start triggering other triggers, it becomes hard to tell what will happen.

As an alternative, wrap access to the table in a stored procedure. For example:

create table TestTable (productId int, price numeric(6,2))
insert into TestTable (productId, price) values (1,5.0)
go
create procedure dbo.IncreasePrice(
    @productId int,
    @newPrice numeric(6,2))
with execute as owner
as
begin
    update  dbo.TestTable
    set     price = @newPrice
    where   productId = @productId
            and price <= @newPrice

    return @@ROWCOUNT
end
go

Now if you try to decrease the price, the procedure will fail and return 0:

exec IncreasePrice 1, 4.0
select * from TestTable --> 1, 5.00
exec IncreasePrice 1, 6.0
select * from TestTable --> 1, 6.00

Stored procedures are pretty easy to read. Compared to triggers, they'll cause you a lot less headaches. You can enforce the use of stored procedures by not giving any user the right to UPDATE tables. That's a good practice anyway.