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

COUNT(*) FROM Inserted

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

Count Inserted table

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

Inserted, deleted pseudo table

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

inserted table Demo

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>

Join Inserted table with real table

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