With the lack of a real lifecycle model for requirements etc i.e with permissions, i have been looking at ways to enforce auditing. for example, we put our usecases through a lifecycle as Proposed, Accepted, In Design, In Development, Frozen. i wanted to track the state changes of these, so added a trigger to SQL server. (first one i ever created).
looking to share ideas on this one. perhaps people have done similar stuff. was wondering doing some permission checking stuff in here to. i.e. only project manager can set things into a certain state. who knows.
This is what i implemented
CREATE TABLE [dbo].[MCL_USE_CASE_STATE_AUDIT]
(
[PreviousState] [nvarchar] (50) ,
[NewState] [nvarchar] (50) ,
[UserName] [nvarchar] (50),
[ChangeDate] [datetime],
[ObjectId] [int]
)
CREATE TRIGGER StateTrigger ON [dbo].[t_object]
FOR UPDATE
AS
begin
if update(Status)
insert dbo.MCL_USE_CASE_STATE_AUDIT ( PreviousState, NewState, UserName, ChangeDate, ObjectId)
select deleted.Status, inserted.Status, user_name(), getDate(), deleted.Object_Id from inserted, deleted where deleted.Object_Type = 'UseCase' and deleted.Object_id = inserted.Object_id
end
and as i said, first trigger on SQL server so if you can tidy up the definition then let me know
