SQLServer中的觸發(fā)器基本語法與作用

CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR [DELETE, INSERT, UPDATE]
AS
T-SQL語句
GO

/*創(chuàng)建insert觸發(fā)器，在上網(wǎng)記錄表recordInfo上創(chuàng)建插入觸發(fā)器*/
create trigger tr_insert_recordInfo
on recordInfo
for insert
as
/*定義變量，用于臨時(shí)存儲(chǔ)插入的會(huì)員號(hào)、電腦編號(hào)和卡的編號(hào)*/
declare @cardId char()
declare @PCId int
declare @CardNumber char()
/*從inserted臨時(shí)表中獲取插入的記錄行的信息，包括電腦的編號(hào)、卡的編號(hào)*/
select @PCId=PCId,@cardId=CardId from inserted
/*根據(jù)電腦編號(hào)修改電腦的使用狀態(tài)*/
update PCInfo set PCUse= where PCId=@PCId
/*根據(jù)卡的編號(hào)查詢會(huì)員號(hào)*/
select @CardNumber=CardNumber from cardinfo where CardId=@cardid
/*顯示上機(jī)成功的信息*/
print '上機(jī)成功！會(huì)員號(hào)是：'+@CardNumber+'機(jī)器號(hào)是：'+convert(char(),@PCId)
go
----插入測(cè)試數(shù)據(jù)，會(huì)員號(hào)為的上機(jī)
set nocount on --不顯示sql語句影響的記錄行數(shù)
declare @CardId int ---聲明一個(gè)存儲(chǔ)卡的編號(hào)的變量
---根據(jù)會(huì)員號(hào)查處卡的編號(hào)
select @cardId=cardid from cardinfo where cardNumber='c' 
---向recordInfo表中插入一條記錄信息，卡的編號(hào)、電腦編號(hào)和上機(jī)時(shí)間
insert into recordInfo(cardId,PCId,beginTime) values(@cardId,,getDate())
----查看結(jié)果
select * from recordInfo
select * from PCInfo

---創(chuàng)建delete觸發(fā)器,在上網(wǎng)記錄表RecordInfo上創(chuàng)建刪除觸發(fā)器
create trigger tr_delete_recordInfo
on recordInfo
for delete
as
if exists(select * from sysobjects where name='backRecordInfo')
----如果backrecordInfo表存在，就添加記錄即可
insert into backRecordInfo select * from deleted
else
----創(chuàng)建backRecordInfo表，從deleted中獲取被刪除的數(shù)據(jù)
select * into backRecordInfo from deleted
print'backRecordInfo表備份數(shù)據(jù)成功，備份表中的數(shù)據(jù)為：'
select * from backRecordInfo
go
-------關(guān)鍵代碼------
----測(cè)試delete觸發(fā)器，刪除數(shù)據(jù)
set nocount on
delete from recordInfo
---查看結(jié)果
print'記錄表中的數(shù)據(jù)為：'
select * from recordInfo

-------關(guān)鍵代碼------
create trigger tr_update_recordInfo
on recordInfo
for update
as
declare @beforePCId int
declare @afterPCId int
select @beforePCId =PCId from deleted
select @afterPCId=PCID from inserted
---根據(jù)電腦編號(hào)修改使用狀態(tài)-----
---根據(jù)以前使用的電腦編號(hào)把電腦的使用狀態(tài)改為：
update PCInfo set PCUse= where PCId=@beforePCId
---根據(jù)現(xiàn)在使用的電腦編號(hào)把電腦的使用狀態(tài)改為:
update PCInfo set PCUse= where PCId=@afterPCId
----顯示電腦換機(jī)成功
print'換機(jī)成功！從'+convert(varchar(),@beforePCId)+'號(hào)電腦換到'+convert(varchar(),@afterPCId)+'號(hào)電腦'
go
/*測(cè)試update觸發(fā)器，修改電腦編號(hào)*/
--顯示更改前,記錄表中的數(shù)據(jù)
print'更改前，記錄表中的數(shù)據(jù)'
select * from recordInfo
--顯示更改前，電腦表中的數(shù)據(jù)
print'更改前，電腦表中的數(shù)據(jù)'
select * from PCInfo
set nocount on
---把電腦號(hào)為的改為
update recordInfo set PCId= where PCId= 
---查看結(jié)果
print'更改后，記錄表中的數(shù)據(jù)'
select * from recordInfo
print'更改后，電腦表中的數(shù)據(jù)'
select * from PCInfo

---創(chuàng)建update觸發(fā)器，在上網(wǎng)記錄表recordInfo上創(chuàng)建修改（列）觸發(fā)器
create trigger tr_updateColum1_recordInfo
on recordInfo
instead of insert
as
declare @cardbalance int --聲明用于存儲(chǔ)用戶余額的變量
declare @CardId int --聲明用于存儲(chǔ)用戶卡的編號(hào)的變量
declare @PCId int --聲明用于存儲(chǔ)電腦編號(hào)的變量
---inserted臨時(shí)表中獲取插入的記錄行信息，包括電腦編號(hào)、卡的編號(hào)
select @cardId=cardId,@PCId=PCId from inserted
select @cardbalance=cardBalance from cardInfo where CardId=@CardId
print'您的余額為：'+convert(varchar(10),@cardBalance) ---打印余額信息
if(@cardBalance2) ---判斷余額多少，看能否正常上機(jī)
print'余額小于元，不能上機(jī)。請(qǐng)盡快充值！'
else
----根據(jù)電腦的編號(hào)修改電腦的使用狀態(tài)更改為正在使用
update PCInfo set PCUse=1 where PCId=@PCId
----向recordInfo表插入上機(jī)記錄
insert into recordInfo(cardId,PCId,beginTime)values(@CardId,@PCId,getdate())
print'上機(jī)成功'
-------關(guān)鍵代碼------
set nocount on
declare @cardId int ---聲明一個(gè)存儲(chǔ)卡的編號(hào)的變量
---根據(jù)會(huì)員號(hào)查出卡的編號(hào)
select @cardId=cardId from cardInfo where cardNumber='c001'
----向recordInfo表中插入一條記錄信息，卡的編號(hào)、電腦的編號(hào)和上機(jī)時(shí)間
insert into recordInfo(cardId,PCId,beginTime)values(@cardId,1,getdate())
select * from recordInfo
select * from PCInfo