Mega Code Archive
Count for DELETED
<![CDATA[
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
]]>