The @@identity returns the last identity insert for a particular user according to the documentation I read, not the last identity insert in the database. Of course if other triggers fire after your insert the @@identity value may be the result of another trigger insert, and not what you expect.
>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 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Protect your mail server with built in anti-virus protection. It's not only good for you, it's good for everybody. http://www.houseoffusion.com/banners/view.cfm?bannerid=39 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:189873 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
