使用 SQL Server 2000 查詢分析器,建立表、插入資料、建立觸發器

create database book

use book

create table personLike
(
 id int primary key,
 likeContent varchar(100) not null
)

insert into personLike values(1,'體育')
insert into personLike values(2,'旅遊')
insert into personLike values(3,'遊戲')
insert into personLike values(4,'上網')
insert into personLike values(5,'交友')
insert into personLike values(6,'電影')
insert into personLike values(7,'購物')
insert into personLike values(8,'遊說')
insert into personLike values(9,'程式')
insert into personLike values(10,'集會')

create table vote
(
 voteid int primary key,
 voteItem varchar(100) not null,
 voteNum int default 0
)

insert into vote values(1,'魏高峰',0)
insert into vote values(2,'王冠',0)
insert into vote values(3,'賴占飛',0)

use vote

delete table voteMaster

use book

create table voteMaster
(
 voteID int primary key,
 voteTitle varchar(150) not null,
 voteSum int default 0
)
insert into voteMaster values(1,'選舉工會主席!',0)
insert into voteMaster values(2,'對網站建設的意見!',0)

select * from voteMaster

create table voteDetails
(
 --id int identity(1,1) primary key, --自增
 voteID int foreign key references voteMaster(voteID),
 voteDetailsID int not null,
 voteItem varchar(20) not null,
 voteNum int default 0
 primary key(voteID,voteDetailsID)
)
insert into voteDetails values(1,1,'魏高峰',0)
insert into voteDetails values(1,2,'賴占飛',0)
insert into voteDetails values(1,3,'穀源',0)

insert into voteDetails values(2,1,'非常好',0)
insert into voteDetails values(2,2,'好',0)
insert into voteDetails values(2,3,'一般',0)
insert into voteDetails values(2,4,'要改進',0)

select * from voteDetails

-- 建立一個觸發器
create trigger updateMaster
on voteDetails                   
for update
as
begin
 update voteMaster set voteSum = voteSum +1 where voteID=(select top 1 voteID from inserted)
end
--使用一個子查詢，使 voteID = 剛插入的那條記錄的ID