Can anybody help me with the following on my MS SQL Server 2000 database.
1. All tables should have a lastModificationDate column. Any changes and inserts should have the system time updated with a trigger or so. We shouldn’t be inserting the value using SQL statements into this column.
2. There shouldn’t be any deletes on the table. Any deleted records should be marked as inactive or deleted, so it won’t come in queries, but should be physically present in the tables.
3. A modification log table, which will carry the table name, the column identifier, user modified, old value and the timestamp.
1. I dont think u need to have triggers for this - just create ModificationDate field & set it as NOTNULL & Default value as CurrentDate
2. Do u want this also to happen in backend with out frontend access then... go for triggers for delete statement and set that to 1 or 0, but anyway u need to change the 'select' query to check the Falg feild.
3. Do u want this to happen in trigger... I would say pls dont go for that... I doesnt know well about ur appln.... but if it have Millions of data and for maintenance purpose u run a backend query(update/delete with trigger attached)... just imagine how this trigger will work....