Using a trigger to remove dupe reocrds

[Guest_imported]

I want to create a trigger on a table to do the following:

Using an ID value, if any rows with the ID to be inserted exists, then insert those rows into a dupe table, then remove those rows from the original table, than complete the insert of the row to be inserted:

PSUEDO-code
(test tables)
t_data_orig
id as unique number Primary
data1 as varchar
data2 as varchar
updDate as date

t_data_dupes
id as number (dupes allowed)
data1 as varchar
data2 as varchar
updDate as date


set a trigger on t_data_original

/*If a record exists for this id, move the orignal record to the dupe table*/
select into t_data_dupes
id, data1,data2,updDate
from t_data_orig
where inserted.id = id

/*Delete the duplicates from the orignal table*/
delete t_data_orig
where id = inserted.id


I am coming from Oracle so am unfamiliar with SQL Server syntax. In Oracle, this type of trigger would cause a mutating table data error. Can this be done in SQL Server?

 

[fluteplr]

I think you can do what you want in SQL2000 using an instead of trigger. In SQL7 (assuming the id field is a primary key ) the insert would fail before the trigger is run.