Logging deletion of rows in table sql server -

i have been searching way log deletion of rows table.

tried log record changes in sql server in audit table didn't me.

i have song list database, log table has columns: title / artist / year / position / sentindate .

there list songs years 1999 2014, , every year has 2000 songs (top2000 called in netherlands).

basically log table should once year has been deleted:

i need basic way trigger-log when deletes year list of 1999-2014.

i hope have informed enough understand, if not try explain in more detail.

a trigger rejects or accepts each data modification transaction whole.

using correlated subquery in trigger can force trigger examine modified rows 1 one.

examples

a. use after insert trigger

the following example assumes existence of table called newsale in pubs database. create statement newsale:

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

if want examine each of records trying insert, trigger conditionalinsert analyzes insert row row, , deletes rows not have title_id in titles.

create trigger conditionalinsert on sales after insert if (select count(*) titles, inserted titles.title_id = inserted.title_id) <> @@rowcount begin    delete sales sales, inserted    sales.title_id = inserted.title_id ,       inserted.title_id not in          (select title_id          titles)    print 'only sales records matching title_ids added.' end 

when unacceptable titles have been inserted, transaction not rolled back; instead, trigger deletes unwanted rows. ability delete rows have been inserted relies on order in processing occurs when triggers fired. first, rows inserted sales table , inserted table, , trigger fires.