Sql Server 事务、存储过程、触发器

前言

Sql Server 中除了增删查改之外，常用的还有事务、存储过程、触发器等。同时，在事务、存储过程、触发器的批量sql中，常常需要声明变量、使用if、循环语句。本文首先介绍了变量的使用方法、if、循环语句的使用，然后简单的总结了事务、存储过程、触发器的用法。

开始

step.1 声明变量、if、循环

声明变量、变量赋值、输出的使用示例：

declare @a int;
set @a = 123;
print @a;
set @a=@a+1;
print @a;

同时，也可以将print @a 换成 select @a 来查看@a的值。

if的使用：

declare @a int;
set @a=123;
if @a>100
    begin
        print 1;
        print 2;
    end
else
    begin
        print 3;
        print 4;
    end

同时，if常常与exists连用，比如我们有student表：

针对上表可以这么用if：

if exists (select * from student where name = '皮卡丘')
    begin
        update student set age= 20 where name='皮卡丘';
    end
else
    begin
        print '哎';
    end

循环常用普通while循环以及游标循环，先来看普通while循环，还是针对student表，可以这么使用while循环：

declare @id int;
set @id=1;
while @id<5
begin
	update student set age=age+1 where id=@id;
	set @id=@id+1;
end

游标循环通常用来遍历表：

declare @row int;
declare cursor_stu cursor
    for (select id from student); -- 定义游标
open cursor_stu; --打开游标
fetch next from cursor_stu into @row;
while @@FETCH_STATUS=0 --表示游标没到末尾
begin
    update student set age=age+1 where id=@row;
    fetch next from cursor_stu into @row;
end
close cursor_stu; --关闭
deallocate cursor_stu; --释放

step.2 事务

事务是指一组sql语句必须同时执行或者同时不执行，比如转账时，一个账户金额减少和另外一个账户金额增加必须同时被执行或者同时被不执行，而不能仅仅执行一个。

事务用 Begin Tran 开启，由commit提交。以转账100块为例：

Begin Tran
    update table set money=money-100 where name='小明';
    update table set money=money+100 where name='小红';
commit

只有碰到commit语句时，两行update才会生效。

若将上面的commit改为rollback，则update不会执行。下面的代码示例了rollback。

Begin Tran
    update table set money=money-100 where name='小明';
    update table set money=money+100 where name='小红';
rollback

同时，在一个事务中，可以用save来记录一个状态点并指定状态点的名称(下面示例中的sp1即为状态点名称)，并且可以通过状态点名称来rollback到指定状态点：

Begin Tran
    update table set money=money-100 where name='小明';
    save transaction sp1
    update table set money=money+100 where name='小红';
    rollback transaction sp1
commit

上面的代码仅仅将小明账户减少了100块，而没有给小红账户增加。

step.3 存储过程

存储过程可以看做是在sql server端的函数，可以通过存储过程名来执行多行sql。可以带输出参数、输出参数。

编写存储过程实现加法：

create proc myadd
    @a int,
    @b int,
    @c int out
as
begin    
    set @c=@a+@b;
end

调用过程如下：

declare @result int;
exec myadd 1,2,@result out;
print @result;

编写存储过程实现转账：

create proc ZZ
    @user1 nvarchar(50),
    @user2 nvarchar(50),
    @money int
as
begin    
    begin tran
        update table set money=money-@money where name=@user1;
        update table set money=money+@money where name=@user2;
    commit
end

step.4 触发器

触发器（trigger ），在对表进行操作（ insert，delete， update）时，可以被触发。

下面是一个简单例子，在student表中插入数据时将输出插入数据中的name：

create trigger trig_insert
on student --指定表名
after insert --在插入数据后触发
as
begin
    declare @newName nvarchar(50);
	select @newName=name from inserted;
	print @newName;
end

对于delete触发器，我们可以用deleted获取已经删除的数据

create trigger trig_delete
on student 
after delete
as
begin
    select id as 已删除的学生编号,name,age
    from deleted
end

或者禁止删除操作：

create trigger trig_delete1
on teachers 
after delete
as
begin
    rollback
end

对于update,可以用deleted,inserted获取更新前后的数据

create trigger trig_update
on student
after update
as
begin
    select id as 更新前学生编号,name as 更新前学生姓名 from deleted
    select id as 更新后学生编号,name as 更新后学生姓名 from inserted
end

上面的几个触发器均是after触发器，在操作之后触发。下面简单介绍instead of 触发器。instead of触发器会直接替代原本的操作。例如：

create trigger trig_insert1
on student --指定表名
instead of insert --在插入数据后触发
as
begin
    declare @name nvarchar(50),@gender nvarchar(50),@age int;
	select @name=name,@gender=gender,@age=age from inserted;
	if(@age>20)
	begin
		print '年龄大于20岁，不允许插入';
	end
	else
	begin
		insert into student(name,gender,age) values (@name,@gender,@age); 
	end
end

上面的代码示例中，对于插入数据，首先要判断age是否大于20，如果大于20则禁止插入，否则才执行原来的代码。

删除触发器使用drop：

drop trigger trig_update;

结束

本文简单的总结了事务、存储过程、触发器的用法。