Logging deletion of rows in table sql server

Question

I have been searching for a way to log the deletion of rows from a table.

Tried this Log record changes in SQL server in an audit table but it didn't help me.

I have a song list database, the log table has the columns: Title / Artist / Year / Position / SentinDate .

There is a list with songs from the years 1999 to 2014, and every year has 2000 songs (top2000 is what it is called in The Netherlands).

Basically what the log table should look like once a certain Year has been deleted:

I need a basic way trigger-log when someone deletes a certain year from the list of 1999-2014.

I hope to have informed enough for you to understand, if not I will try to explain in more detail.

score 1 · Answer 1 · answered Jun 01 '15 at 08:46

A trigger rejects or accepts each data modification transaction as a whole.

Using a correlated subquery in a trigger can force the trigger to examine the modified rows one by one.

Examples

A. Use an AFTER INSERT trigger

The following example assumes the existence of a table called newsale in the pubs database. This the CREATE statement for newsale:

CREATE TABLE newsale
   (stor_id char(4),
   ord_num varchar(20),
   date datetime,
   qty smallint,
   payterms varchar(12),
   title_id tid)

If you want to examine each of the records you are trying to insert, the trigger conditionalinsert analyzes the insert row by row, and then deletes the rows that do not have a title_id in titles.

CREATE TRIGGER conditionalinsert
ON sales
AFTER INSERT AS
IF
(SELECT COUNT(*) FROM titles, inserted
WHERE titles.title_id = inserted.title_id) <> @@ROWCOUNT
BEGIN
   DELETE sales FROM sales, inserted
   WHERE sales.title_id = inserted.title_id AND
      inserted.title_id NOT IN
         (SELECT title_id
         FROM titles)
   PRINT 'Only sales records with matching title_ids added.'
END

When unacceptable titles have been inserted, the transaction is not rolled back; instead, the trigger deletes the unwanted rows. This ability to delete rows that have been inserted relies on the order in which processing occurs when triggers are fired. First, rows are inserted into the sales table and the inserted table, and then the trigger fires.

I'm not trying to stop the deletion, I want to insert every deleted row into the Log table, with column names Title / Artist / Year / Position — stefan, Jun 01 '15 at 08:50

score 0 · Answer 2 · answered Jul 01 '15 at 18:46

Simply create an INSTEAD OF DELETE trigger ! In that trigger, you have a "virtual" table called deletedwhich contains all records which are to be deleted.

So in the trigger, you can just insert all records contained in deleted to your log table, and then you delete the records from our table. (this will then be a DELETE statement with a join to the deleted table)

Logging deletion of rows in table sql server

2 Answers2