July 3, 2003 at 9:41 am
I have a table with an Insert Trigger which successfully creates another record in a log table.
What I'm not sure about is how I update a record in the log table with changes made to a specific record in the first table.
I have created an Update Trigger as shown below. Comments as to what I'm doing that is wrong would be appreciated.
Thanks
Andy
CREATE TRIGGER t_UpdatemageCostImport ON dbo.ImageImportCosts FOR UPDATE AS
/* Update Each Time We Import a Image Cost Record
This may have been done via the Insert Trigger but for Acrs the Values are not available at that time
due to limitations with the insert statement used directly From Parsed Xml File */
Declare @RecordId As int
Declare @FileCreated As DateTime
Declare @SourceFileName As Varchar(25)
Declare @FileType As Varchar(10)
Begin Transaction
Select @FileType = FileType, @SourceFileName = SourceFileName , @FileCreated= FileCreatedDateTime ,@RecordId = RecordId from Updated
Update ImageCostFileImportLog Set FileCreatedDateTime = @FileCreated, FileType = @FileType, SourceFileName = @SourceFileName Where RecordId = @RecordId
Commit Transaction
July 3, 2003 at 12:15 pm
I would try:
Update LT Set LT.FileCreatedDateTime = Updated.FileCreatedDateTime, LT.FileType = Updated.FileType, LT.SourceFileName = Updated.SourceFileName
FROM Updated inner join ImageCostFileImportLog LT on LT.RecordId = Updated.RecordId
The advantage of this is it still works if you update the data table with a batch query, your method will only work if you update one record at a time.
Not sure you need the tranaction bit, triggers use an implied transaction and transactions can be tricky to get right
Philip
July 4, 2003 at 12:28 am
There are only two table variables used in all trigger statements: the deleted table and the inserted table - there is no Updated (checkout BOL:"Using the inserted and deleted Tables").
So, in this case, you could perform an update on your log table using the rows in the inserted table for all rows in the deleted table (ie. where inserted.RecordId = deleted.RecordId and deleted.RecordId = log.RecordId) - assuming RecordId cannot be updated (if it can then you will need to rely on something else that can't be updated when the RecordId is being updated - to test for this the IF UPDATED(column) clause).
This has the benefit that your trigger can now handle multi-row updates. I would drop the explicit transaction statements.
see how this goes
jason
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply