Click here to Skip to main content
15,881,938 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I create a trigger on table after insert and insert the data from current table to new table using temp INSERTED table. Data is inserted but some column data is not correct. for example i have distance column in both old and new table and suppose in one row of old table i inserted new record with distance 34.45 but after trigger is fired the new table's distance column have 37.21.

i don't understand what is the problem and how to get rid of it.

Please help me.

[ADDED (from comments)]
SQL
CREATE TRIGGER [dbo].[Shortest_Exit_Path]
ON [dbo].[Exit_Path]
AFTER INSERT
AS
IF (@@RowCount > 0)
BEGIN

    INSERT into Shortest_Exit_Path([ProjectId]
           ,[ExitPath]
           ,[PhysicalExitPath]
           ,[ExitPathCoordinates]
           ,[Distance]
          )
        SELECT [ProjectId]

           ,[ExitPath]
           ,[PhysicalExitPath]
           ,[ExitPathCoordinates]
           ,[Distance]
            FROM Inserted


END
Posted
Updated 23-Mar-11 2:55am
v3
Comments
Wendelius 23-Mar-11 7:14am    
Could you post the trigger code. Without seeing the code it's just speculating what might be wrong.
Balwant.mnd 23-Mar-11 8:15am    
CREATE TRIGGER [dbo].[Shortest_Exit_Path]
ON [dbo].[Exit_Path]
AFTER INSERT
AS
IF (@@RowCount > 0)
BEGIN

INSERT into Shortest_Exit_Path([ProjectId]
,[ExitPath]
,[PhysicalExitPath]
,[ExitPathCoordinates]
,[Distance]
)
SELECT [ProjectId]

,[ExitPath]
,[PhysicalExitPath]
,[ExitPathCoordinates]
,[Distance]
FROM Inserted


END

So basically you're just copying the row. First thing that comes in mind is that the Exit_Path table is later updated and that update is not reflected to Shortest_Exit_Path
The same kind of situation could happen because of deletions.
 
Share this answer
 
Comments
Balwant.mnd 23-Mar-11 23:41pm    
no, their is no updation and deletion on each table.
Wendelius 24-Mar-11 16:26pm    
Ok, and you're sure you don't have any other triggers affecting this, either on this or the target table? If not then I would suspect that your insert in the trigger is going fine but there's some false data (duplicates etc) in the target table.
Are you directly putting the value in distance column or calculating it some where on Font End or Back End?
Otherwise Mika give the good reason of what possible happened....
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900