в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:11Z

1 версия

Новая страница

== 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>

← Предыдущая	Версия 13:46, 26 мая 2010
(нет различий)

SQL Server/T-SQL Tutorial/Trigger/Deleted table - История изменений

в 13:46, 26 мая 2010

Admin: 1 версия