|
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>
2> CREATE TABLE OrderDetails (
3> OrderID int NOT NULL ,
4> ProductID int NOT NULL ,
5> UnitPrice money NOT NULL DEFAULT (0),
6> Quantity smallint NOT NULL DEFAULT (1),
7> Discount real NOT NULL DEFAULT (0)
8> )
9> GO
1>
2> CREATE TRIGGER OrderDetailNotDiscontinued
3> ON OrderDetails
4> FOR INSERT, UPDATE
5> AS
6> IF EXISTS
7> (
8> SELECT 'True'
9> FROM Inserted i
10> JOIN Products p
11> ON i.ProductID = p.ProductID
12> WHERE p.Discontinued = 1
13> )
14> BEGIN
15> RAISERROR('Order Item is discontinued. Transaction Failed.',16,1)
16> ROLLBACK TRAN
17> END
18> GO
1> drop TRIGGER OrderDetailNotDiscontinued;
2> GO
1> drop table OrderDetails;
2> GO
1> drop table Products;
2> GO
|