mark a row for delete,but not delete - Bank Scenario

[MS Techie]

In a bank , there is nothing that gets deleted ,but gets marked for delete
and the next time a select statement is issued ,those "rows marked for
delete" should not be shown in the future. Of course they would be a history
table which keeps a track of DML statements (Create, Update,Delete
operations) issued and holds the details for every transaction... Now this
being said. HOW DO WE IMPLEMENT THIS SCENARIO,

I believe this is the approach to be taken. Please correct me if I am wrong.

For every table ,which has data, we need to create a history table for
every table, which has the same structure as the original table. Now for
every DML operation like Insert, Update or delete that takes place on the
original table, we are supposed to have triggers which would insert values to
the history tables.
Now assuming that there is no permission for the user to delete (i.e. GRANT
DELETE permission is taken off),how do we mark something for delete and still
capture the delete triggers to work. How is it exactly implemented in the
bank scenarios ?

[j***@gmail.com]

Post by MS Techie
In a bank , there is nothing that gets deleted ,but gets marked for delete
and the next time a select statement is issued ,those "rows marked for
delete" should not be shown in the future. Of course they would be a history
table which keeps a track of DML statements (Create, Update,Delete
operations) issued and holds the details for every transaction... Now this
being said. HOW DO WE IMPLEMENT THIS SCENARIO,
I believe this is the approach to be taken. Please correct me if I am wrong.
 For every table ,which has data, we need to create a history table for
every table, which has the same structure as the original table. Now for
every DML operation like Insert, Update or delete that takes place on the
original table, we are supposed to have triggers which would insert values to
the history tables.
Now assuming that there is no permission for the user to delete (i.e. GRANT
DELETE permission is taken off),how do we mark something for delete and still
capture the delete triggers to work. How is it exactly implemented in the
bank scenarios ?

My opinion is that keeping tables which are in effect exact copies, as
transaction logs, can be very inefficient and hog a lot of space in
your database.

Not knowing the specifics of your requirements, I'll try and offer
some suggestions as to how I've handled things like this in the
past.

First, the "marked for delete" issue. What I'd do is add a column to
your original table, of a BIT datatype, and call it something like
"Delete_Fl". Then, when this row needs to be marked for delete, set
the flag to true, and in a view, only display the records where the
Delete_Fl is false.

Regarding transaction logs, there are many ways it can be done. It,
or course, depends a lot on how much data you need to keep. One idea
I had was to, first, add a surrogate key (identity column) to your
main table, and then whenever an Insert, Update, or
"Delete" (remember, the Delete is just the state change of the delete
flag), you'd insert a row into the transaction log with the surrogate
key and flag identifying what DML operation took place. If you need
more specific info in the log, you could add more columns, or perhaps
compile all the changes into a large varchar column, concatenating
them in the trigger or stored procedure.

Make use of surrogate keys in this scenario, though. They are your
friend, and they help you avoid replicating data where it's not
necessary.