Locking table in trigger

[jby1]

Hi

I am writing a trigger to write updated in a table to a History table. On the history table, there is a HistorySequence number for each instance of a record from the original table.

In the trigger, I need to get the largest HistorySequence already entered, and update. Then I can insert the new History record with the correct HistorySequence. I would like to lock the History table for this process so that the sequence number stay in synch.

Can anybody tell me how I should do this?

Thanks.

 

[jby1]

This is what I have some up with.

If anyone sees any flaw, please let me know!

CREATE TRIGGER trig_update_Contributor ON [dbo].[Contributor] 
FOR UPDATE 
AS

declare @NextHistoryId int

begin transaction 

select @NextHistoryId = max(c.HistoryId) + 1 
from ContributorHistory c with (tablockx)
inner join inserted i
on c.ContributorId = i.ContributorId

insert into ContributorHistory (ContributorId, HistoryId, HistoryUser, HistoryDate, 
	ElementId, ContributorTypeId, Name, Royalty, UpdatedUser, UpdatedDate)
    select ContributorId, @NextHistoryId, UpdatedUser, GetDate(), 
	ElementId, ContributorTypeId, Name, Royalty, UpdatedUser, UpdatedDate
    from inserted

commit transaction

 

[vongrunt]

Other than for multiple update issues, this won't work for first historical entry (then MAX() returns NULL).

------
heisenbug: A bug that disappears or alters its behavior when one attempts to probe or isolate it
schroedinbug: A bug that doesn't appear until someone reads source code and realizes it never should have worked, at which point the program promptly stops working for everybody until fixed.

 

[jby1]

Thanks for your response.

I have already included an INSERT trigger for the first record, this sets the Sequence number to 1.

Seems to work ok.

It is more the locking side of it that I am unsure of.

Thanks again.

(PS Love the sig!!)

 

[jby1]

Just to update, I have put the Insert and Update into the same trigger, and use IsNull to set the History number to 1 if there was no previous record.

 

[paulstrovsky]

I apologize if I'm misinterpreting the situation (I am probably oversimplifying), but I think there is better way to deal with this situation...

You could add an identity key to the history table. It would be easy to derive the sequence of events when querying this table- just sort by the history identity key.

The ContributorHistory table would use ContributorHistoryID as its key but still show ContributorID.

It would be very easy to show the history of a contributor with a simple query-

SELECT *
FROM ContributorHistory
Where ContributorID=(Whatever)
order by ContributorHistoryID