Trigger FOR INSERT, UPDATE : Trigger for insert « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Trigger for insert

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