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

1. SQL Server / T-SQL
2. Trigger
3. Trigger for update

Trigger for FOR INSERT, UPDATE

 


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 OrderDetailIsProduct
3>       ON OrderDetails
4>       FOR INSERT, UPDATE
5>    AS
6>       IF EXISTS
7>          (
8>           SELECT 'True'
9>           FROM Inserted i
10>           LEFT JOIN Products p
11>              ON i.ProductID = p.ProductID
12>           WHERE p.ProductID IS NULL
13>          )
14>       BEGIN
15>          RAISERROR('Order Item Must Be a Valid Product' ,16,1)
16>          ROLLBACK TRAN
17>       END
18> GO
1> drop TRIGGER OrderDetailIsProduct;
2> GO
1> drop table OrderDetails;
2> GO
1> drop table Products;
2> GO

 

Related examples in the same category