How to move a row from a table to another table if a Column's value changes in SQL?

I have two tables, Hosts, and UnusedHosts. Hosts has开发者_StackOverflow中文版 17 columns, and UnusedHosts has 14 columns, where the first 12 is the same as in Hosts, and the 13th is a UserName, who moved a host to UnusedHosts, and the 14th is a date, when he did it. In Hosts there is a Column Unused which is False. I want do the following. If i change in Hosts this value to True, then it should automatically removed to UnusedHosts.

How can i do this? Could someone provide some example?

P.S.: My SQL knowledge is very small, i can use only very simple selects, updates, inserts, and delete commands.

Thanks!

There's two main types of query in SQL Server - the AFTER and the INSTEAD OF. They work, much as they sound - the AFTER performs your original query, and then runs your trigger. The INSTEAD OF runs your trigger in place of the original query. You can use either in this case, though in different ways.

AFTER:

create trigger hosts_unused
on Hosts
after UPDATE
as

insert into UnusedHosts
select h.<<your_columns>>...
from Hosts h
where h.unused = 1 --Or however else you may be denoting True


delete from Hosts
where unused = 0 --Or however else you may be denoting False
GO

INSTEAD OF:

create trigger hosts_unused
on Hosts
instead of UPDATE
as

insert into UnusedHosts
select i.<<your_columns>>...
from inserted i
where i.unused = 1 --Or however else you may be denoting True

delete h
from inserted i inner join
    Hosts h on i.host_id = h.host_id
where i.unused = 1 --Or however else you may be denoting True

update h
set hosts_column_1 = i.hosts_column_1,
    hosts_column_2 = i.hosts_column_2,
    etc
from inserted i inner join
    Hosts h on i.host_id = h.host_id
where i.unused = 0 --Or however else you may be denoting False
GO

It's always important to think of performance when applying triggers. If you have a lot of updates on the Hosts table, but only a few of them are setting the unused column, then the AFTER trigger is probably going to give you better performance. The AFTER trigger also has the benefit that you can simply put in , INSERT after the after UPDATE bit, and it'll work for inserts too.

Check out Books Online on the subject.