I found it simpler to create an identical table to hold the deleted
records. In the before delete trigger of original table execute
something like

insert into Table_Deleted select * from Table where
RecordUniquenum = Old.RecordUniqueNum

Now to get some extra audit information, add say
two extra fields, DataTime, UserName

and append to the trigger

update Table_Deleted set
DateTime = Current_DateTime, UserName = Current_User

This way your working data set table don't carry the extra
overhead of deleted records. You can auto purge the deleted
records say after so many months. And if you want to
report on both the original and deleted, just join the 2
tables in a select.

----------------------------------------------------------

----- Original Message -----
From: "Martijn Tonies" <m.tonies@...>
To: <ib-support@yahoogroups.com>
Sent: Monday, January 06, 2003 1:14 AM
Subject: Re: [ib-support] Re: Flagging a deletion rather than delete


| Didier,
|
|
| > > I use an ACTIVE boolean field in the table, which indicates whether
the
| > > record should be considered deleted or not. We don't delete our
deletes
| > > (if you see what I mean), we just make them inactive.
| > >
| >
| > Yes, I agree this is the way to go but I was hoping to be able to mark
| > the record as deleted in a before delete trigger event and somehow
| > prevent the actual deletion from happening. That would save me to have
| > to explain to others (there are quite a few different apps written by
| > different teams using different tools dealing with the same DB) that
| > to delete a record, you in fact have to update that field, they'll see
| > that as a sort of twisted logic, even though it's not.
| >
| > So any pointer in that direction would help me a lot.
|
| How about a procedure that does the "delete" for you - instead of
| DELETE FROM table, create a procedure DELETE_table with parameters
| with the primary key value. This way, you're keeping the delete logic
| in the database... Next, create a BEFORE DELETE trigger that raises
| an exception along the lines of "cannot delete, use stored procedure
| proc_name"
|
| So instead of doing a DELETE FROM, you need to call the procedure.
| The trigger with the exception ensures no-one is actually deleting
anything.
|
|
| With regards,
|
| Martijn Tonies
| InterBase Workbench - the developer tool for InterBase & Firebird
| Firebird Workbench - the developer tool for Firebird
| Upscene Productions
| http://www.upscene.com
|
| "This is an object-oriented system.
| If we change anything, the users object."
|
|
|
| To unsubscribe from this group, send an email to:
| ib-support-unsubscribe@egroups.com
|
|
|
| Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
|
|
|