|
2>
3> CREATE TABLE Products (
4> ProductID int IDENTITY (1, 1) NOT NULL ,
5> ProductName nvarchar (40) NOT NULL ,
6> SupplierID int NULL ,
7> CategoryID int NULL ,
8> QuantityPerUnit nvarchar (20) NULL ,
9> UnitPrice money NULL,
10> UnitsInStock smallint NULL,
11> UnitsOnOrder smallint NULL,
12> ReorderLevel smallint NULL,
13> Discontinued bit NOT NULL
14> )
15> GO
1> CREATE TRIGGER ProductIsRationed
2> ON Products
3> FOR UPDATE
4> AS
5> IF EXISTS
6> (
7> SELECT 'True'
8> FROM Inserted i
9> JOIN Deleted d
10> ON i.ProductID = d.ProductID
11> WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
12> AND d.UnitsInStock - i.UnitsInStock > 0
13> )
14> BEGIN
15> RAISERROR('Cannot reduce stock by more than 50%% at once.',16,1)
16> ROLLBACK TRAN
17> END
18> GO
1>
2> drop TRIGGER ProductIsRationed;
3> GO
1>
2> drop table Products;
3> GO
1>
|