Rollback transaction in a trigger : Trigger « Trigger « SQL Server / T-SQL Tutorial






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








22.1.Trigger
22.1.1.The syntax of the CREATE TRIGGER statement
22.1.2.exits if the price column has not been updated.
22.1.3.Define variables in a trigger
22.1.4.Update table in a trigger
22.1.5.Check @@ROWCOUNT in a trigger
22.1.6.Rollback transaction in a trigger
22.1.7.RAISERROR in trigger
22.1.8.Disable a trigger
22.1.9.Enable a trigger
22.1.10.Check business logic in a trigger
22.1.11.Check record matching in a trigger
22.1.12.Table for INSTEAD OF Trigger for Logical Deletes
22.1.13.INSTEAD OF INSERT trigger for a table
22.1.14.Trigger Scripts for Cascading DELETEs