I took the liberty of rewriting your trigger for you. Just a hint, when
posting a question like this, include the table create statements if you
can. It makes writing the answer code easier (and in most cases correct).
Remember that in triggers you have the inserted and the deleted tables. You
can join them with any other tables in the database, so you don't have to do
select statements to get the values. This also makes the trigger handle
multiple inserts / updates. Remember that trigger in SQL Server one get
executed once for the transaction. So in order to update multiple rows you
have to use a LOOP, CURSOR, or know how to write some AWESOME SQL ;).
I also remember that UPDATE can use the FROM operator to update multiple
rows. Thus your trigger can be rewritten into just one little UPDATE
statemenet. How cute and compact. :P
CREATE TRIGGER dbo.MyTest ON dbo.UserTime
FOR INSERT
AS
UPDATE ProjectCosts
SET
InternalCost = ProjectCosts.InternalCost + (UR.UserRate *
i.ActualHours),
ActualHours = ProjectCosts.ActualHours + i.ActualHours
FROM
Orders
INNER JOIN inserted i
ON Orders.OrderID = i.OrderID
INNER JOIN UserRates UR
ON i.UserID = UR.UserID
-----Original Message-----
From: Robert Bailey [mailto:[EMAIL PROTECTED]
Sent: Monday, January 10, 2005 7:14 PM
To: CF-Talk
Subject: SQL Triggers
A little rusty at the triggers I see, but here is what I got:
CREATE TRIGGER dbo.MyTest ON dbo.UserTime AFTER INSERT AS
DECLARE @UserTimeProjID Int , @OrderProjID Int, @InternalCostAdd
Int,@ActualHours Int
Select @UserTimeProjID, @OrderProjID, @InternalCostAdd,@ActualHours
(
SELECT UT.ProjectID, Orders.ProjectID, UR.UserRate * UT.ActualHours ,
UT.ActualHours
FROM Orders INNER JOIN
UserTime UT ON Orders.OrderID = UT.OrderID INNER
JOIN
UserRates UR ON UT.UserID = UR.UserID AND
UT.UserTimeID = @@IDENTITY
)
UPDATE ProjectCosts
SET InternalCost = ProjectCosts.InternalCost + @InternalCostAdd,
ActualHours = ProjectCosts.ActualHours + @ActualHours
WHERE ProjectID = @UserTimeProjID
OR ProjectID = @OrderProjID
go
I am not sure if the @@identity works for the inserted row and if my
select statement is correct. Any pointers?
Robert Bailey
Software Engineer
813-230-9967 \\ mobile
[EMAIL PROTECTED]
www.recruitmax.com
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble
Ticket application
http://www.houseoffusion.com/banners/view.cfm?bannerid=48
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189872
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
