use wroxdb
go

--
--   Name:  orders_u
--
--   Description: Update Trigger to provide optimistic concurrency locking
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'orders_u')
BEGIN
    DROP TRIGGER orders_u
END
GO
 
PRINT 'orders_u'
GO
CREATE TRIGGER orders_u
ON orders
FOR UPDATE
AS

    IF EXISTS (SELECT *
    				FROM deleted d
    				JOIN inserted i
               	ON	d.order_no = i.order_no
               WHERE i.adm_rev_no <> d.adm_rev_no+1)
      BEGIN
         ROLLBACK TRAN
         RAISERROR 211001 'Order has already been updated by another User'
         RETURN
      END
 
GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'orders_u')
   PRINT  '>>> Created TRIGGER orders_u <<<'
GO


