黑马程序员—触发器的实例运用

------------------ Windows Phones 7手机开发、.Net培训、期待与您交流！---------------------- 

通过今天在看投票功能的时候，学习一下了触发器的运用，感觉还好吧，这是我在项目中实际运用的一个例子：投票.

 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培训、期待与您交流！----------------------