COUNT(*) FROM Inserted : Inserted table « Trigger « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Trigger
3. Inserted table

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