SQL Server trigger : delete duplicate then Insert

0
votes

Hello I am trying to create a history table.. My code pretty much works fine but my reports' queries compute for the summation of rows with the same column value (x_id)

Now I would like to create a trigger that would first DELETE the old rows (For instance, row 1's value is (5x,100))

Then my trigger would insert (5x, 200), (5x, 300)

I would like to delete first the (5x, 100) column before inserting my (5x, 200) and (5x, 300)

I tried putting a DELETE statement before my INSERT in my trigger but I end up with just 1 row inserted on my history table, it deletes also the newly added rows except for the last row..

If it matters, my trigger is AFTER UPDATE and DELETE.

Also, table1 and table1_hist have the exact same column definition.

 CREATE TRIGGER trig_table1_hist
 ON table1
 AFTER UPDATE, DELETE
 AS
     DELETE FROM table1_hist 
     WHERE table1_hist.t_id IN (SELECT t_id FROM inserted)

     INSERT INTO table1_hist 
        SELECT * 
        FROM inserted

I tried this but it deletes the newly added rows too with the same primary key. Now If i don't delete, my table would then have (5x,100) (5x,200) (5x,300) But I need the (5x,100) to be disappeared because my reports would still include the (5x,100)

2
sqlsql-serversql-server-2008triggers
Please post your attempted trigger code. It's not entirely clear what you're trying to do or what you've attempted and is going wrong here. - Dan Field
@DanField There. I tried to make a similar code to what I am using. Hope it helps. - Ziddorino

2 Answers

0
votes

You're probably inserting 2 rows in table 1 so your trigger is triggered 2 times (the first one deletes (5x,100) and the second deletes (5x,200))

I don't know in which scenario are you, but I would recommend to add a column with the date you inserted the data in you table1_hist and show reports according to those dates. Deleting rows in a history table is usually bad idea

0
votes

You need logic to check whether it was an UPDATE or DELETE. Delete will use the psuedo-table deleted rather than inserted. For example:

CREATE TRIGGER trig_table1_hist
  ON table1
  AFTER UPDATE,DELETE

 AS
 IF (EXISTS(SELECT 1 from deleted))
 BEGIN
    DELETE FROM table1_hist where table1_hist.t_id IN (SELECT t_id FROM   deleted)
    INSERT INTO table1_hist 
    SELECT * FROM deleted
ENd

IF (EXISTS(SELECT 1 FROM inserted))
 BEGIN
    DELETE FROM table1_hist where table1_hist.t_id IN (SELECT t_id FROM   inserted)
    INSERT INTO table1_hist 
    SELECT * FROM inserted
ENd

Aside from that, you need to make sure you're using a unique key - pick the primary key for table1, don't use a field that may or may not repeat - otherwise, you'll end up with unintended consequences (like putting the wrong row(s) into the history table).