SQL Trigger to update a field on insert if the inserted value conflicts with the unique constraint

Question

I have a Table called Albums which contains a field IsDeleted and a field "Name". Name is a unique key. My Question is:

Can I create a trigger which updates IsDeleted to False if it is set to True and the same name is inserted into the table by the user?

Of course the insert statement should somehow be changed to an update statement.

I'm Using MSSQL 2008 and I'm very new to triggers and SQL ingeneral

Thanks for your help!

Answer 1

Yes, you can do this with an instead of trigger. Example:

create trigger albums_ins_trig on albums instead of insert as begin
    -- update albums which already exist and are being inserted again
    update albums set IsDeleted=0 where IsDeleted=1 
    and exists (select * from inserted where albums.name=inserted.name)
    -- insert new albums
    insert into albums select * from inserted 
    where not exists (select * from albums where albums.id=inserted.id) 
end