The UPDATE() Function : Update function « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Update function

5>
6> CREATE TABLE Products (
7>      ProductID int NOT NULL ,
8>      ProductName nvarchar (40) NOT NULL ,
9>      SupplierID int NULL ,
10>     CategoryID int NULL ,
11>     QuantityPerUnit nvarchar (20) NULL ,
12>     UnitPrice money NULL,
13>     UnitsInStock smallint NULL,
14>     UnitsOnOrder smallint NULL,
15>     ReorderLevel smallint NULL,
16>     Discontinued bit NOT NULL
17> )
18> GO
1> INSERT Products VALUES(1,'F',15,4,'10 - 999 g pkgs.',61.5,66,6,6,6)
2> INSERT Products VALUES(2,'M',14,4,'24 - 888 g pkgs.',34.8,74,7,7,7)
3> INSERT Products VALUES(3,'R',17,8,'24 - 777 g jars',17,171,0,5,0)
4> INSERT Products VALUES(4,'L',4,7,'5 kg pkg.',10,4,20,5,0)
5> INSERT Products VALUES(5,'R',12,1,'24 - 0.5 l bottles',1.23,445,0,25,0)
6> INSERT Products VALUES(6,'L',23,1,'500 ml',18,57,1,20,0)
7> INSERT Products VALUES(7,'O',12,2,'12 boxes',13,23,0,15,0)
8> go

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)
1>
2>    create TRIGGER ProductIsRationed
3>       ON Products
4>       FOR UPDATE
5>    AS
6>       IF UPDATE(UnitsInStock)
7>       BEGIN
8>       IF EXISTS
9>          (
10>           SELECT 'True'
11>           FROM Inserted i
12>           JOIN Deleted d
13>              ON i.ProductID = d.ProductID
14>           WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
15>              AND d.UnitsInStock - i.UnitsInStock > 0
16>          )
17>       BEGIN
18>          RAISERROR('Cannot reduce stock by more than 50%% at once.',16,1)
19>          ROLLBACK TRAN
20>       END
21>       END
22> GO
1>
2> drop TRIGGER ProductIsRationed;
3> GO
1>
2> drop table Products;
3> GO