Is there a mechanism for soft delete at database level

72
March 22, 2018, at 11:28 AM

Is there a mechanism for soft delete at database level in MySQL and Oracle, Such that:-

1) All delete queries soft delete the data

2) All fetch queries do not fetch the soft delete data without any explicit where condition of filtering out the soft deleted data.

3) All update queries fire only on data that has not been soft deleted.

Answer 1

These examples are for MySQL, but the same examples apply also to Oracle.

For queries, you can set up a view that only shows non deleted rows. Then point your normal SELECTs to that view, and SELECTs which need to handle also soft deleted rows to the underlying table.

I think that there is no soft delete support for built-in for other operations.

Creating a view

CREATE VIEW my_active_stuff AS 
SELECT *
where deleted is null
FROM my_stuff_table;

Querying the view. Returns only undeleted rows.

SELECT *
FROM my_active_stuff
WHERE ...

You can read more about views in the MySQL docs.

Answer 2

In Oracle there's a component called Workspace Manager that can do all this plumbing.

https://docs.oracle.com/database/121/ADWSM/long_intro.htm#ADWSM010

"Workspace Manager also creates a view on the original table (), as well as INSTEAD OF triggers on the view for insert, update, and delete operations. When an application executes a statement to insert, update, or delete data in a version-enabled table, the appropriate INSTEAD OF trigger performs the actual operation. When the view is accessed, it uses the workspace metadata to show only the row versions relevant to the current workspace of the user."

Honestly, I'd build it into the application. There's often a lot of business logic involved (eg Mr Smith has been soft-deleted, but needs to be inserted again. Mr Jones needs to be REALLY deleted as per some privacy legislation...).

READ ALSO
how to update the status automatically after 15 days?

how to update the status automatically after 15 days?

I have a date and status column in mysql tablecurrently status of post is = Sent/Received

92
How T Get Specific Column From a Specific Row In PHP/MySql

How T Get Specific Column From a Specific Row In PHP/MySql

How T Get Specific Column From a Specific Row In PHP/MySql I got a table "users" and I have to fetch column "profile" from row where name = $Name CAn anyone help me?

77
Design database to store user permision by group. Improve database performance

Design database to store user permision by group. Improve database performance

I am having trouble designing the database as follows

76