通过今天在看投票功能的时候,学习一下了触发器的运用,感觉还好吧,这是我在项目中实际运用的一个例子:投票.
drop table VoteMaster
drop table VoteDetails
/*主表*/
create table VoteMaster
(
VoteID INT PRIMARY KEY,/*主键ID*/
VoteTitle nvarchar(50) not null,/*投票主题*/
VoteSum int not null default(0),/*投票总数*/
)
insert into VoteMaster(VoteID,VoteTitle,VoteSum) values(1,'你喜欢黑马的那些老师?',0)
insert into VoteMaster(VoteID,VoteTitle,VoteSum) values(2,'wp7你觉得难学吗?',0)
select*from voteMaster
/*从表*/
create table VoteDetails
(
VoteID int foreign key referencesVoteMaster(VoteID),/*外键*/
VoteDetailsID int not null,/*投票流水号*/
VoteItem varchar(50) not null,
VoteNum int not null default(0),/*投票项数*/
primary key(VoteID,VoteDetailsID)/*联合主键*/
)
select*from VoteDetails
truncate table VoteDetails
insert into VoteDetails values(1,1,'杨老师',0)
insert into voteDetails values(1,2,'邹老师',0)
insert into voteDetails values(1,3,'张老师',0)
insert into voteDetails(VoteID,VoteDetailsID,VoteItem,VoteNum) values(2,1,'非常难',0)
insert into voteDetails(VoteID,VoteDetailsID,VoteItem,VoteNum) values(2,2,'很难',0)
insert into voteDetails(VoteID,VoteDetailsID,VoteItem,VoteNum) values(2,3,'一般',0)
/****触发器创建*****/
create trigger updateMaster//updateMaster --为触发器名
on VoteDetails --为指定在那张表中进行触发器
for update --触发器执行的操作
as
begin
updateVoteMaster set VoteSum=VoteSum+1 where VoteID=(select top 1 VoteID frominserted) --触发器执行的语句
end
/*模拟点击投票*/
update VoteDetails set VoteNum=VoteNum+1 where VoteDetailsID=1AND VoteID=1
update VoteDetails set VoteNum=VoteNum+1 where VoteDetailsID=2AND VoteID=1
显示效果如下:
drop trigger updateMaster /*删除触发器*/
------------------Windows Phones 7手机开发、.Net培训、期待与您交流!----------------------