SQL Server觸發(fā)器和事務(wù)用法示例
發(fā)布日期：2021-12-18 12:55 | 文章來(lái)源：gibhub
本文實(shí)例講述了SQL Server觸發(fā)器和事務(wù)用法。分享給大家供大家參考，具體如下：
新增和刪除觸發(fā)器
alter trigger tri_TC on t_c for INSERT,delete as begin set XACT_ABORT ON declare @INSERTCOUNT int; declare @DELETECOUNT int; declare @UPDATECOUNT int; set @INSERTCOUNT = (select COUNT(*) from inserted); set @DELETECOUNT = (select COUNT(*) from deleted); set @UPDATECOUNT = () if(@INSERTCOUNT > 0) begin insert into t_c2 select * from inserted; end else if(@DELETECOUNT > 0) begin delete t_c2 where exists(select temp.cid from deleted temp where temp.cid=t_c2.cid); end end
更新觸發(fā)器和事務(wù)
事務(wù)主要用在數(shù)據(jù)的保護(hù)，在多表更新時(shí)，事務(wù)保存所有事務(wù)下的更新語(yǔ)句就不會(huì)提交，數(shù)據(jù)也就不能更新成功
alter trigger tri_TC_Update on t_c
for update
as
begin
declare @delcount int;
set @delcount = (select count(*) from deleted);
if(@delcount > 0)
begin
begin transaction triUpdate --定義事務(wù)
declare @cname varchar(100);
select @cname = cname from inserted; --保存更新后的內(nèi)容
update t_c2 set cname = @cname where cid = (select cid from deleted); --更新
if (@@error <> 0)
begin
rollback transaction triUpdate; --事務(wù)回滾
end
else
begin
commit transaction triUpdate; --事務(wù)提交
end
end
end
存儲(chǔ)過(guò)程
if(exists(select name from sysobjects s where s.name='pro_fun' and s.type='p')) drop procedure pro_fun go create procedure pro_fun as select * from table go exec pro_fun
游標(biāo)
declare @qybh varchar(10) declare cur cursor for select distinct qybh from PJ_EnterpriseInput open cur fetch next from cur into @qybh while @@fetch_status = 0 begin print(@qybh) fetch next from cur into @qybh end close cur deallocate cur
視圖
alter view CreateView as select qybh from CreateView go
定義方法
alter function funName(@str1 varchar(10),@str2 varchar(10))
returns varchar(10)
as
begin
declare @returnStr varchar(10)
set @returnStr = 'false'
if(@str1 > @str2)
set @returnStr = 'true'
return @returnStr
end
select dbo.funName(... , ...)
定義表變量
declare @qybhTable table (id varchar(32),qybh varchar(30)) insert into @qybhTable select id,qybh from PJ_EnterpriseInput select * from @qybhTable
case when then 條件統(tǒng)計(jì)時(shí)的使用
select sum(case when z.watchName='注冊(cè)監(jiān)理工程師' then 1 else 0 end), sum(case when z.watchName='xinza' then 1 else 0 end), sum(case when z.watchName='監(jiān)理員' then 1 else 0 end) from zu_corjl z right join zu_corjltemp t on t.corID=z.corID
希望本文所述對(duì)大家SQL Server數(shù)據(jù)庫(kù)程序設(shè)計(jì)有所幫助。
版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非maisonbaluchon.cn所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信