trigger question

[dajohnson1s]

Hello,

I am about to launch an application for inter-University communications. What I want to do, is make a 'log' of the data that is being changed. While I am doing daily backups, I fear getting a call saying "we accidentally deleted everything...and the newsletter goes out in 10 min...".

What I thought about doing was adding a trigger, so when something is removed/ deleted it will copy the value to a 'backup table' for easy recall. Also I would like to log which user made the change.

Assuming this is the appropriate use for a trigger, I am a little confused with some of the insert examples. Do I have to update every row?

Thanks

[ckeyrouz]

If you need to monitor every change in the database, yes you need to update every row.

[dajohnson1s]

ckeyrouz,

I don't believe I would need to monitor every row of the database, but I am not totally sure.

Essentially what I want to do is set up a way to monitor who is changing the data. Users do login, and the plan was to write the username, date, and what the changed (original and new) to a backup table.

After reading about triggers, seemed like the way to go, but I am not sure.

Thanks

[ckeyrouz]

The easiest thing to be done is to back up the database on daily basis so whenever anything wrong happens you would still have a backup from the day before.

If the database is not that big you can do 2 backups (automated) a day.

I don't know if this solution helps you in your need.

[bazz]

if you have used a composite primary key, any updates or new records, will not remove the original but, instead, will create a new one. Then you can have an additional column, which notes the 'end date'. this should be set to (today's date - 1) when any new record or update is added and will therefore be easily found as an archived record. After x months/years the db can be set actually to delete such archived records.

And (to answer the specific of your question), you can have two more col names for recording who made the record and when they did it.

last updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
last updated by varchar(99) not null - put the username here from the login details.

bazz

[dajohnson1s]

Ok, I found out today why they (the bosses) are asking for this trigger. Due to one generic login, somebody has been deleting things...and since IT lets each department administer their own servers/databases...most of the data is long gone.

With the exception of my 2 apps, no data backups exist. (My apps are the only ones that are not unsecured on the network for everybody to see -- department wise) So, since everybody in the department will have access...and I am validating with Active Directory...they are trying to catch the fool who is deleting everything...and essentially my trigger will provide them with evidence to fire them. (I assume it must be somebody pretty high up).

Now, my admin skills are weak. I am doing a manual backup almost daily, and have recently discovered cron jobs (plan to get one of them going).

bazz, I am not using composite keys.

With the new information, I assume they want something in the background that watches for changes...like "remove article"...I guess I could open a connection to write the data and snag the username from the session...but I was hoping to have a 'trigger' or some other procedure do it for me. :)

I am still not completely sure how to tackle this 'monitoring' addition. From what I had read, it would be a good idea to get these items:

username, dateModified, origItem, newItem

and store all that in a "backup" table.

Now the question is, do I continue and look into triggers for each of the insert/delete actions? Or do I go ahead and just add another connection to the new table and write that stuff anyway when the buttons are clicked? (currently I have a "remove article" button to delete the article from the database)

Thanks

[bazz]

I would not profess to be the most knowledgable on this but, I would think about these steps as a logical (non-programming), attempt:-

1. change the deletion script to one which would archive the data instead.
2. change the db privileges for all logins to not allow deletions of any sort.
3. having done that, the system could be made operate as it does now (so the person wouldn't be any wiser about the changes).
4. then when any action is performed by any script, the details of that are saved to a text file (username,login, timestamp, action performed,on which table).
5. That should mean that if they do manage to hack in and delete tables or records, the data you have captured won't be in the db.

If you are able to duplicate the db and make the login go to this duped db, the data in the real one should be safe.

If you are on an office network such that people have to be in the office to login, don't forget to monitor people's location at specific times of the day. matching those times up with when data is deleted might help to isolate the innocent people. even giving some people errands so that they aren't at a pc, might help speed up that isolation process.

Those are my suggestons as they sprang to mind.

hth

bazz

[dajohnson1s]

Bazz,

Thanks, this is basically what I am going to do, when 'removing' an item, it will insert that value along w/ the username and possibly timestamp to a 'maintenance table'.

Not sure how to grab the new value, yet, but I will figure that one out.

Originally, I thought it would slow the app down to do this, but I got asked, "why do you care...", also I saw the users list...and there is only 12 users, 3 of which will actually use it on the regular.

Again, thanks.

[bazz]

well I think you should find it easy to narrow it down probably to 3 people then. And from there it should be reasonably straightforward.


One other thing; better to have stored too much info than not enough. You'll maybe need enough not only, to prove who was doing it but, to rebutt any excuses they may throw up such as 'it couldn't have been me that time because I was not here, for example. You might need to store the computer id (if you can get it), to prove it was their machine too. And then, is there a policy in the office regarding maintasining the security of each person's passwords, by them? There ought to be.

good luck,
bazz.