Advertisements
Home > SQL Server Questions > Database TRIGGER on delete – MSDN TSQL forum

Database TRIGGER on delete – MSDN TSQL forum


–> 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.


Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: