Preventing duplicate rows
-
Hi:
I have a trigger on a table, monitoring updates and inserts, storing them in a history table.
The are some applications that issue UPDATE's against the base table, even without modifying any column. These updates are also "captured" by the trigger, inserting duplicate rows in the history table.
The table has 33 columns, so I can't create an unique index ignoring dups.
Is there any simple logic to control the dup row before inserting it?
Many thanks in advance.
Regards, Hernan.
-
You can use the UPDATE method that test if an update was made to that column in your trigger.
IF UPDATE(myFirstColumn)
INSERT INTO myHistoricalTable...
-
There could be 2 approaches:
1. Application has to check that the values that are to be inserted / updated are different from any of the existent rows, something like this:
If Exists (select * from MyTable where col1 = value1 and col2 =value2........)
Begin
<Put your code here to notify a user that a row with these values already exist>
End
2. History table has to have some sort of RowID column.
Regards,Yelena Varsha
-
Tried both.
Using UPDATE(col1) OR UPDATE(col2)... for all columns doesn't fire the trigger, even if I really update a column.
Using exists needs the update command to be issued twice to work. Weird....
-
How about something like...
-- insert trigger
insert into history
select *
from inserted
-- update trigger, assuming PK does not change.
insert into history
select I.*
from inserted I
join deleted D on I.PKCol = D.PKCol
where not (
I.Col1 = D.Col1
and I.Col2 = D.Col2
and I.Col3 = D.Col3
...
 
-
Ken:
Joining deleted with inserted worked. Thanks.
I still don't know why joining deleted with history needs the update to be issued twice to work.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply