SQL with Manoj

–> Question:

Today we saw that some rows got deleted in our Dataware House. Normally rows are never deleted, they are just marked as deleted because we want to keep the history. Now there are two important questions :

How can we detect who or what deleted this rows ?

How can we prevent that they are being deleted ?

We have around 450 tables and I think around 250 ETL packages.
 

–> My Answer:

You can create a INSTEAD OF DELETE (DML) TRIGGER on that table, and inside this trigger apply UPDATE statement to soft-delete the records.

With this every DELETE statement fired on that particular table will be an UPDATE.

To Track who deleted those records you can create a DDL TRIGGER that will identify and log the users who issued DELETE statement.
 

Ref Link.