SQL Server/T-SQL Tutorial/Trigger/Inserted table

Материал из SQL эксперт
Версия от 13:24, 26 мая 2010; Admin (обсуждение | вклад) (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>

Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Trigger/Inserted_table&oldid=6832»