November 21, 2015 at 10:13 am
Hi, I am new in sql server. I am using TSQL2012 database. I have added a new column in processedOrderCount in Hr.Employees table. And created a trigger on Sales.Orders table that whenever orderId is updated it automatically updated processedOrderCount in Hr.Employees. There is a problem that the orderId is the identity column we can't update it. Kindly tell how can we work with identity column? the code of my trigger is:
If Object_id ('Sales.trig_Calculate_OrderProcessed', 'tr') is not null
Drop Trigger Sales.trig_Calculate_OrderProcessed
go
Create Trigger Sales.trig_Calculate_OrderProcessed
ON Sales.Orders
After Insert,Update,Delete
AS
Begin
If update (orderid)
Begin
Select empid,count(orderid) totalOrders
Into #Temp
From Sales.Orders
Where empid in (Select empid from Inserted
Union All
Select empid From Deleted)
Group By empid
Update E
Set E.processedOrderCount = t.totalOrders
From Hr.Employees E
Inner join #Temp t on e.empid = T.empid
Drop Table #Temp
End
End
November 21, 2015 at 12:27 pm
Well, you can't update identity columns, so when do you want that trigger to fire? On what operation? When what happens?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 22, 2015 at 3:15 pm
I believe you should consider rowversion. See https://msdn.microsoft.com/en-us/library/ms182776.aspx
November 23, 2015 at 3:15 pm
Drop the IF Update(...
(Also drop the temp table.)
Try something like this (untested as you didn't provide data...)
DROP TRIGGER Sales.trig_Calculate_OrderProcessed;
GO
CREATE TRIGGER Sales.trig_Calculate_OrderProcessed ON Sales.Orders
AFTER INSERT, DELETE
AS
BEGIN
WITH A AS (
SELECT empid
, COUNT(orderid) totalOrders
FROM Sales.Orders
WHERE empid IN ( SELECT empid
FROM Inserted
UNION ALL
SELECT empid
FROM Deleted )
GROUP BY empid
)
UPDATE Hr.Employees
SET Employees.processedOrderCount = A.totalOrders
FROM Hr.Employees
INNER JOIN A ON A.empid = Employees.empid
AND Employees.processedOrderCount <> A.totalOrders;
END;
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply