SQL Server觸發(fā)器和事務用法示例
發(fā)布日期：2021-12-18 12:55 | 文章來源：gibhub
本文實例講述了SQL Server觸發(fā)器和事務用法。分享給大家供大家參考，具體如下：
新增和刪除觸發(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ā)器和事務
事務主要用在數(shù)據(jù)的保護，在多表更新時，事務保存所有事務下的更新語句就不會提交，數(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 --定義事務
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; --事務回滾
end
else
begin
commit transaction triUpdate; --事務提交
end
end
end
存儲過程
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
游標
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)計時的使用
select sum(case when z.watchName='注冊監(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
希望本文所述對大家SQL Server數(shù)據(jù)庫程序設計有所幫助。
版權(quán)聲明：本站文章來源標注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務器上建立鏡像，否則將依法追究法律責任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學習參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
相關(guān)文章
關(guān)注官方微信