这里有新鲜出炉的 SQL Server 教程,程序狗速度看过来!
SQL 是英文 Structured Query Language 的缩写,意思为结构化查询语言。SQL 语言的主要功能就是同各种数据库建立联系,进行沟通。按照 ANSI(美国国家标准协会) 的规定,SQL 被作为关系型数据库管理系统的标准语言。
SQL Server 即 Microsoft SQL Server 。
这篇文章主要介绍了 SQL Server 触发器和事务用法, 结合实例形式分析了 SQL Server 触发器、事务、存储过程、游标、视图等的相关定义与使用方法, 需要的朋友可以参考下
本文实例讲述了 SQL Server 触发器和事务用法。分享给大家供大家参考,具体如下:
新增和删除触发器
- 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
更新触发器和事务
事务主要用在数据的保护,在多表更新时,事务保存所有事务下的更新语句就不会提交,数据也就不能更新成功
- 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; --保存更新后的内容
- 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 条件统计时的使用
- select
- sum(case when z.watchName='注册监理工程师' then 1 else 0 end),
- sum(case when z.watchName='xinza' then 1 else 0 end),
- sum(case when z.watchName='监理员' then 1 else 0 end)
- from zu_corjl z
- right join zu_corjltemp t on t.corID=z.corID
希望本文所述对大家 SQL Server 数据库程序设计有所帮助。
来源: http://www.phperz.com/article/17/0817/339286.html