How should I do this - keeping record of status changes

[darbid]

Access 2003 Front end
SQL Server 2000 back end

Bound Ctrl1 = the status of a record. (drop down list of status choices)
Bound Ctrl2 = upon status changing the date the status changed.

I would like to create a new table in the back end which keeps a record of the status' and dates that they change for each record. Thus one could query this new table to see a timeline of status changes over time.

I am posting here as I am wondering if I could do this totally in the backend with a trigger i think it is called.

If possible my first thought is I would not want the trigger to always fire upon a change but only if the change stays for more than a few minutes.

 

[SQL_Hell]

A trigger will fire on update, so probably not the best thing to use.

If you dont have already, you could add a field to your table for the updated dates, such as

StatusUpdateDT datetime

and then run a job on a schedule to add the records into the new table after 2 minutes (or whatever timescale suits)

The job would be something like

INSERT INTO ChangeLog (blah,blah,blah)--for example
SELECT status, StatusUpdateDT FROM MainTable
WHERE StatusUpdateDT > dateadd(min,-5,getdate())

 

[WayneRyan]

SQL Hell,

Why not use the Update trigger for the table?

If Update(StatusField)
   Insert Into tblAuditTrail(ThePK, TheStatus, TheDate)
   Select ThePK, TheStatus, GetDate() From Inserted

Wayne

 

[darbid]

StatusUpdateDT datetime

and then run a job on a schedule to add the records into the new table after 2 minutes (or whatever timescale suits)

The job would be something like

INSERT INTO ChangeLog (blah,blah,blah)--for example
SELECT status, StatusUpdateDT FROM MainTable
WHERE StatusUpdateDT > dateadd(min,-5,getdate())

This sounds exactly what I was thinking. So my question is how do I do this? If that is going to take too long then could you give me some theory words to google so that I can teach myself.

SQL Hell,
Why not use the Update trigger for the table?

If Update(StatusField)
   Insert Into tblAuditTrail(ThePK, TheStatus, TheDate)
   Select ThePK, TheStatus, GetDate() From Inserted

Wayne

Won't this trigger immediately an update? So if we have a user that plays around with the status and changes it to X then Y then thinks oops it should be Z - all these will be in my tblAuditTrail?

 

[WayneRyan]

darbid,

Yes, they'd all go into the audit trail.

I was just wondering why SQL Hell was opposed to the trigger.

He'll be back soon, I bet.

Wayne

 

[SQL_Hell]

darbid,

Yes, they'd all go into the audit trail.

I was just wondering why SQL Hell was opposed to the trigger.

He'll be back soon, I bet.

Wayne

Simply because he wanted the event to fire a on a delay and it wouldn't be easy to do that with a trigger, but however you got me thinking...

Maybe a lightweight 'on update' trigger that puts data in the audit table is the right way to go, and then have a scheduled process (sql server job) that cleans up the audit table and deletes the stuff you dont need. Doing that would also give you the ability to see how often an update is occuring on any given record which might be useful to you.

 

[darbid]

I must admit that since posting the question I have been looking at other options.

I see that my front end can do it too. There are Audittrail examples and I have learnt how to implement them for my two controls, but I get the feeling that my forms are complicated enough.

But still I would like the server to do more work and I thought this was a good time to start.

I do not mind how you do it SQL HELL just please keep our little project simple.

Each record in this database represents a project that can take 1 to 4 years to do. We have about 12 stages of the project and currently we just have a field and date for these, meaning that you can only see the current stage.

Thus the need to now record these in a second table to see how things happened over time.

 

[darbid]

Are you thinking of suggesting a Waitfor delay?

 

[SQL_Hell]

Nope, I've made 2 suggestions... you need to decide which one is right for you and then try to code it yourself.

 

[darbid]

ohh so you have, sorry I was not reading them that way, probably not reading them at all....I do not not know what I was waiting for...ooops

I think the second sounds better as the clean up could be scheduled at a time when the server has a low load and although I have no idea a trigger sounds like a "normal" thing to do.