Writing MySQL DELETE statements

Question

We have MySQL-> Oracle ETL using Informatica, which is great for all but operators DELETE. Unfortunately, it DELETEmakes the record go away, so Informatica never sees it again to delete / expire it in Oracle.

How did people start writing MySQL statements DELETE?

InnoDB (ACID-compatible) tables with unique primary keys for all records ( auto_increment). We use open source MySQL on Windows.

We would prefer not to use a common query log for performance reasons. We would also prefer to save the MySQL binary and not recompile our own special statement DELETE.

Answer 1

A possible solution is to never delete anything from your database. I avoid deleting from the database because information is lost forever. I prefer to mark the information as invalid or outdated by adding the appropriate column to the table.

Another similar solution is to use trigger to insert the record you want to delete into the audit table and then delete the information. Use insert with Informatica to do the same on the Oracle side.