SQL Server/T-SQL Tutorial/Trigger/Deleted table — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
(нет различий)

Текущая версия на 13:24, 26 мая 2010

count for DELETED

   <source lang="sql">

2> CREATE TABLE Orders ( 3> OrderID int IDENTITY (1, 1) NOT NULL , 4> CustomerID nchar (5) NULL , 5> EmployeeID int NULL , 6> OrderDate datetime NULL , 7> RequiredDate datetime NULL , 8> ShippedDate datetime NULL , 9> ShipVia int NULL , 10> Freight money NULL DEFAULT (0), 11> ShipName nvarchar (40) NULL , 12> ShipAddress nvarchar (60) NULL , 13> ShipCity nvarchar (15) NULL , 14> ShipRegion nvarchar (15) NULL , 15> ShipPostalCode nvarchar (10) NULL , 16> ShipCountry nvarchar (15) NULL 17> ) 18> GO 1> CREATE TRIGGER Order2DependsOnOrders 2> ON Orders 3> FOR INSERT, UPDATE, DELETE 4> AS 5> DECLARE @Count int 6> SELECT @Count = COUNT(*) FROM DELETED 7> IF @Count > 0 8> BEGIN 9> IF NOT EXISTS 10> ( 11> SELECT "True" 12> FROM Deleted d 13> LEFT JOIN Orders o 14> ON d.OrderID = o.OrderID 15> WHERE o.OrderID IS NULL 16> ) 17> BEGIN 18> RAISERROR("Record Exists In Orders Table. Delete Cancelled.",16,1) 19> ROLLBACK TRAN 20> END 21> END 22> IF @@ERROR != 0 23> ROLLBACK TRAN 24> SELECT @Count = COUNT(*) FROM INSERTED 25> SELECT "Count is " + CONVERT(varchar,@Count) + " Before Delete" 26> IF @Count > 0 27> BEGIN 28> IF EXISTS 29> ( 30> SELECT "True" 31> FROM Inserted i 32> LEFT JOIN Orders o 33> ON i.OrderID = o.OrderID 34> WHERE o.OrderID IS NULL 35> ) 36> BEGIN 37> RAISERROR("Inserted Record Must exist in the Orders Table",16,1) 38> ROLLBACK TRAN 39> END 40> END 41> IF @@ERROR != 0 42> ROLLBACK TRAN 43> GO 1> drop TRIGGER Order2DependsOnOrders; 2> GO 1> drop table Orders; 2> GO</source>


Trigger for delete using deleted table

   <source lang="sql">

5> CREATE TABLE OrderDetails( 6> ID int NOT NULL, 7> PartID int NOT NULL, 8> Quantity int NOT NULL); 9> GO 1> 2> INSERT INTO OrderDetails VALUES(10001, 11, 12) 3> INSERT INTO OrderDetails VALUES(10001, 42, 10) 4> INSERT INTO OrderDetails VALUES(10001, 72, 5) 5> INSERT INTO OrderDetails VALUES(10002, 14, 9) 6> INSERT INTO OrderDetails VALUES(10002, 51, 40) 7> INSERT INTO OrderDetails VALUES(10003, 41, 10) 8> INSERT INTO OrderDetails VALUES(10003, 61, 35) 9> INSERT INTO OrderDetails VALUES(10003, 65, 15) 10> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE Employees 4> (orderid int NOT NULL, 5> mgrid int NULL, 6> empname varchar(25) NOT NULL, 7> salary money NOT NULL); 8> GO 1> 2> 3> CREATE TRIGGER myTrigger ON Employees FOR DELETE 4> AS 5> IF EXISTS(SELECT * 6> FROM 7> OrderDetails AS OD 8> JOIN 9> deleted AS D ON OD.id = D.orderid) 10> BEGIN 11> RAISERROR("The Employee you are trying to delete have related rows 12~ in OrderDetails. TRANSACTION rolled back.", 10, 1) 13> ROLLBACK TRANSACTION 14> END 15> GO 1> 2> drop table OrderDetails; 3> GO 1> 2></source>