в 13:46, 26 мая 2010

2010-05-26T13:46:16Z

Admin: 1 версия

2010-05-26T10:24:34Z

1 версия

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

== COUNT(*) FROM Inserted==



<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 OrdersFeedsOrders
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> DELETE FROM Orders
10> FROM DELETED i
11> JOIN Orders o2
12> ON i.OrderID = o2.OrderID
13> END
14> IF @@ERROR != 0
15> ROLLBACK TRAN
16>
17> SELECT @Count = COUNT(*) FROM Inserted
18>
19> IF @Count > 0
20> BEGIN
21> INSERT INTO Orders
22> SELECT i.*
23> FROM Inserted i
24> LEFT JOIN Orders o2
25> ON i.OrderID = o2.OrderID
26> WHERE o2.OrderID IS NULL
27> END
28>
29> IF @@ERROR != 0
30> ROLLBACK TRAN
31> GO
Msg 8101, Level 16, State 1, Server J\SQLEXPRESS, Procedure OrdersFeedsOrders, Line 21
An explicit value for the identity column in table "Orders" can only be specified when a column list is used and IDENTITY_INSERT is ON.
1>
1> drop table Orders;
2> GO</source>

== Count Inserted table==



<source lang="sql">
2>
3>
4>
5> CREATE TABLE Customers (
6> CustomerID nchar (5) NOT NULL ,
7> CompanyName nvarchar (40) NOT NULL ,
8> ContactName nvarchar (30) NULL ,
9> ContactTitle nvarchar (30) NULL ,
10> Address nvarchar (60) NULL ,
11> City nvarchar (15) NULL ,
12> Region nvarchar (15) NULL ,
13> PostalCode nvarchar (10) NULL ,
14> Country nvarchar (15) NULL ,
15> Phone nvarchar (24) NULL ,
16> Fax nvarchar (24) NULL
17> )
18> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> 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 rows affected)
1>
2> CREATE VIEW CustomerOrders_vw
3> WITH SCHEMABINDING
4> AS
5> SELECT cu.rupanyName
6> FROM dbo.Customers AS cu
7> GO
1>
2>
3>
4> CREATE TRIGGER trCustomerOrderUpdate ON CustomerOrders_vw
5> INSTEAD OF DELETE
6> AS
7> BEGIN
8> IF (SELECT COUNT(*) FROM Inserted) > 0
9> BEGIN
10> RAISERROR("No matching Orders. Cannot perform insert",10,1)
11> END
12> END
13> GO
1> drop TRIGGER trCustomerOrderUpdate;
2> drop view CustomerOrders_vw;
3> drop table Customers;
4> GO</source>

== Inserted, deleted pseudo table==



<source lang="sql">
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</source>

== inserted table Demo==



<source lang="sql">
7>
8> CREATE TABLE Employees
9> (
10> empid int NOT NULL,
11> mgrid int NULL,
12> empname varchar(25) NOT NULL,
13> salary money NOT NULL,
14> lvl int NULL,
15> hierarchy varchar(900) NULL
16> )
17> GO
1>
2> CREATE TRIGGER myTrigger ON Employees FOR INSERT
3> AS
4> DECLARE @numrows AS int
5> SET @numrows = @@ROWCOUNT
6> IF @numrows > 1
7> BEGIN
8> RAISERROR("Only single row inserts are supported!", 16, 1)
9> ROLLBACK TRAN
10> END
11> ELSE
12> IF @numrows = 1
13> BEGIN
14> UPDATE E
15> SET lvl = CASE
16> WHEN E.mgrid IS NULL THEN 0
17> ELSE M.lvl + 1
18> END,
19> hierarchy = CASE
20> WHEN E.mgrid IS NULL THEN "."
21> ELSE M.hierarchy
22> END + CAST(E.empid AS varchar(10)) + "."
23> FROM
24> Employees AS E
25> JOIN
26> inserted AS I ON I.empid = E.empid
27> LEFT OUTER JOIN
28> Employees AS M ON E.mgrid = M.empid
29> END
30> GO
1>
2> --Testing the myTrigger Trigger
3>
4> INSERT INTO employees(empid, mgrid, empname, salary) VALUES(15, 12, "Sean", $1500.00)
5> GO
(1 rows affected)
1> drop table Employees;
2> GO
1></source>

== Join Inserted table with real table==



<source lang="sql">
2>
3> CREATE TABLE Customers (
4> CustomerID nchar (5) NOT NULL ,
5> CompanyName nvarchar (40) NOT NULL ,
6> ContactName nvarchar (30) NULL ,
7> ContactTitle nvarchar (30) NULL ,
8> Address nvarchar (60) NULL ,
9> City nvarchar (15) NULL ,
10> Region nvarchar (15) NULL ,
11> PostalCode nvarchar (10) NULL ,
12> Country nvarchar (15) NULL ,
13> Phone nvarchar (24) NULL ,
14> Fax nvarchar (24) NULL
15> )
16> GO
1>
2> INSERT Customers VALUES("1","A","Maria", "Sales", "Str. 57", "Berlin" ,NULL,"12209", "Germany","111-1111111","111-1111111")
3> INSERT Customers VALUES("2","M","Joe", "Owner", "Ave. 231","Vancouver" ,NULL,"05023", "Mexico", "(222) 222-3332",NULL)
4> INSERT Customers VALUES("3","H","Thomas", "Sales", "Sq. 111","London" ,NULL,"1D00P", "UK", "(444) 444-4444","(444) 444-4444")
5> INSERT Customers VALUES("4","B","Berg", "Order", "Blv 8","Toronto" ,NULL,"00222", "Sweden", "4444-55 55 65","5555-55 55 55")
6> INSERT Customers VALUES("5","S","Moos", "Sales", "Fort 57","New York" ,NULL,"68306", "Germany","6666-66666","6666-77777")
7> INSERT Customers VALUES("6","F","Cite", "Manager","24 ","Dalles" ,NULL,"67000", "France", "88.60.15.31","88.60.15.32")
8> INSERT Customers VALUES("7","C","Sommer", "Owner", "Araq, 67","Paris" ,NULL,"28023", "Spain", "(91) 555 22 82","(91) 555 91 99")
9> INSERT Customers VALUES("8","P","Leb", "Owner", "12 ","Beijing" ,NULL,"13008", "France", "91.24.45.40","91.24.45.41")
10> INSERT Customers VALUES("9","D","Elizabeth","Manager","23 Blvd.","Tsawassen","BC", "T2F8M4","Canada", "(604) 555-4729","(604) 555-3745")
11> 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 rows affected)
1>
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> GO
1>
2> create TRIGGER OrderHasCustomer
3> ON Orders
4> FOR INSERT, UPDATE
5> AS
6> IF EXISTS
7> (
8> SELECT "True"
9> FROM Inserted i
10> LEFT JOIN Customers c
11> ON i.CustomerID = c.CustomerID
12> WHERE c.CustomerID IS NULL
13> )
14> BEGIN
15> RAISERROR(60000,16,1,"CustomerID","Orders","CustomerID","Customers")
16> ROLLBACK TRAN
17> END
18> GO
1>
2> drop TRIGGER OrderHasCustomer;
3> GO
1>
2> drop table Customers;
3> GO
1> drop table orders;
2> GO</source>

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

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

в 13:46, 26 мая 2010

Admin: 1 версия