| Author |
Topic |
|
KnooKie
Aged Yak Warrior
623 Posts |
 Posted - 2002-06-17 : 06:31:58
|
| I need a way of being able to store any rows that are deleted from any of 15 tables.My current thinking is to have one 'Deleted_Records' table with lots of varchar fields (enough to cater for the largest of the 15 tables) and just adding some SQL to the delete triggers of the 15 tables to write a deleted record(s) into it.I am not anticipating having to read from this table much. Only in case of emergencies i.e. a user makes a bad mistake and deletes accidentally.Am using SQL7.Obviously with cascading deletes quite a few records could be deleted in one delete as it were.I there a better way of handling this scenario ?many thanksPaulEdited by - knookie on 06/17/2002 06:32:35 |
|
|
KnooKie
Aged Yak Warrior
623 Posts |
Posted - 2002-06-17 : 07:45:35
|
| I am being drawn towards creating a separate 'delete' table for each main table and writing the records into these.I could also use a delete flag column in the original tables and just hide the 'deleted' row when the flag is set but this will entail greater front-end changes which i am keen to avoid.Paul |
 |
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-17 : 07:55:24
|
HiOn most stuff I do, I have a status column that defaults to active.I don't delete much, just change it's status to inactive or deleted.It works nicely, and you can always undelete stuff  Damian |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2002-06-17 : 08:12:25
|
| I agree with Merkin. And you can always store away the deleted rows and physically delete them when the tables get too huge.Regards Kalle Dahlberg |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-17 : 08:28:25
|
| if you dont wanna do the dbase way Merkin suggested . you can always use Log Explorer from lumigent.com .if you follow the status way, remember creating views that displays only the undelted record. HTH-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2002-06-17 : 08:34:42
|
quote:
if you dont wanna do the dbase way Merkin suggested
<joking>And WHY would you NOT want to do it my way ? </joking>Damian |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-17 : 09:25:19
|
bcoz its your way  quote:
<joking>And WHY would you NOT want to do it my way ? </joking>
Pity Me , i did suggest a similar thing here  -------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo EmersonEdited by - Nazim on 06/17/2002 09:27:53 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-06-17 : 09:30:47
|
quote:
if you dont wanna do the dbase way Merkin suggested . you can always use Log Explorer from lumigent.com
As long as you never back up your database!Nazim, just out of curiosity, if you back up the log, and since it truncates the log when the backup is complete, how is Log Explorer going to find the deleted rows? How do you restore a deleted row from months past? |
|
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-06-17 : 09:43:38
|
Am sorry , i misread what log explorer does. i was under a impression log explorer backs up the log automatically and keeps track of all the logs . making it powerful enough to track changes done irrespective of the log truncation. might be a Auditing software should be of help. Thanx Rob for correcting .quote:
Nazim, just out of curiosity, if you back up the log, and since it truncates the log when the backup is complete, how is Log Explorer going to find the deleted rows? How do you restore a deleted row from months past?
-------------------------What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-06-17 : 10:17:56
|
quote:
...but this will entail greater front-end changes which i am keen to avoid....
You can probably get around some of this by renaming the table and creating a view which has the same name as the original table. The view could be written to expose only the set of non-deleted (?!?) data.<O> |
|
|
|
izaltsman
A custom title
1139 Posts |
Posted - 2002-06-17 : 11:03:05
|
quote:
Nazim, just out of curiosity, if you back up the log, and since it truncates the log when the backup is complete, how is Log Explorer going to find the deleted rows? How do you restore a deleted row from months past?
Technically, if you keep your log backups from months past, you will be able to do it. Log Explorer can read from backup files as well as your current log. But I do think that audit tables/triggers would be a better choice than Log Explorer in this situation. Edited by - izaltsman on 06/17/2002 11:06:11 |
|
|
|
|
Storing deleted rows - the best method ?