触发器

触发器的分类

DML触发器

是指触发器在数据库中发生DML事件时执行。数据操纵语言DML事件包括对表或视图中数据进行操作的insert、update、delete语句。
在SQL SERVER 2008中，DML触发器通过使用两个逻辑表DELETED和INSERTED来实现。这两个表是建立在数据库服务器的内存中，数据库用户只有只读的权限。DELETED和INSERTED表的结构和触发器所在的数据表的结构是一样的。这两个临时表只能用在触发器的代码中，当触发器执行完成后，它们也就会被自动删除。

INSERTED表

用于存放INSERT操作新插入的数据和UPDATE语句执行更新后的记录。比如你插入一条数据，那么就会把这条记录插入到INSERTED表。

DELETED表

存放DELETE操作删除前和UPDATE更新前的数据。
可通过这两种临时表所记录的数据来判断所执行的操作是否符合其他约束条件。

DDL触发器

DDL触发器：是指当数据库中发生DDL事件时执行的触发器。数据定义语言DDL事件是指在表或索引中的create、alter、drop等语句。如修改表，删除列，新增表，新增列等。

登录触发器

当用户登录SQL SERVER实例时，在身份验证阶段完成之后且用户会话实际建立之前激发。

触发器的代码实现

创建一个触发器

DML触发器有两种类型，AFTER和 INSTEAD OF。
AFTER是指只有当引发触发器的SQL语句都已执行成功，并且所有约束检查也已成功通过以后才执行触发器
仅指定 FOR 关键字，则默认为AFTER。不能在视图上定义AFTER 触发器。
INSTEAD OF表示执行触发器而不是执行触发 SQL语句，从而替代触发语句的操作。简单来说，INSTEAD OF表示触发器在触发它的SQL语句执行之前执行。需要在触发器内重新调用执行触发该触发器的SQL语句以完成对数据表的操作
每个 Insert、Update 或 Delete语句最多可以定义一个 INSTEAD OF 触发器

要求：创建实现如下功能的触发器，如果转账金额大于银行卡当前余额，输出“你的余额小于转账金额,转账失败”，事务回滚。
分析：
转账时我们要更新银行卡，而触发器中的临时表INSERTED表存放了UPDATE语句执行更新后的记录，并且这个表和要更新的表BankCard的结构是一样的。

首先从临时表INSERTED中选择更新后的balance列的值

判断该值是否小于0，如果小于0，说明余额小于转账金额，交易失败，事务回滚。

if exists (select name from sysobjects where name = 'tg_transact_before' and type = 'TR')
    drop trigger tg_transact_before
drop trigger tg_transact_before
GO
create trigger tg_transact_before --创建触发器
    on bankcard
    instead of update
as 
begin
    declare @cardid int
    select @cardid = cardid from deleted --记录银行卡号
    
    declare @balance_after money
    select @balance_after = balance from inserted
    if(@balance_after < 0)
        begin 
            print '你的余额小于转账余额, 转账失败'
            rollback
        end
    else 
        begin
            update bankcard set balance = @balance_after where cardid = @cardid
        end
end	

update bankcard set balance = -1 where cardid = 10000003

课上写的乱七八糟

create trigger trbankcard_quqian
on bankcard
instead of update 
as
begin
    print '存取钱'
end

update bankcard set balance = 1
select * from bankcard

alter trigger trbankcard_quqian
on bankcard 
instead of update 
as 
begin
    declare @cardid int, @money money;
    select @cardid = cardid from deleted;
    select @money = balance from inserted;
    if(@money > 0) 
        begin 
            update bankcard set balance = @money where cardid = @cardid
            print 'update succeed'
        end
    else 
        print '失败，余额不足';
    
end

set nocount on --不显示x行受影响
update bankcard set balance = balance - 1 where cardid = 10000004;

update bankcard  set statusID = 2 where cardid = 10000004;

存储过程

定义

存储过程（Procedure）是一组能够完成特定功能的一组SQL语句的集合，经编译后存储在数据库中，它可以接受参数、返回状态值和参数值，并且可以嵌套调用。
存储过程中可以包含逻辑控制语句和数据操纵语句，它可以接受参数，输出参数、返回单个或多个结果集和返回值，也可以没有返回值。

优点

存储过程的优点：
1、存储过程允许模块化编程
存储过程创建后，可以在程序中被多次调用执行，存储过程可被数据库专业人员创建和修改，并保存在数据库中，对调用存储过程的外部应用程序源代码却毫无影响，极大的提高了程序的可移植性。
2、执行速度快
如果某一操作包含大量的T-SQL语句代码并且需要多次执行，那么存储过程的执行效率要比批处理快得多。因为存储过程是预编译的，在首次运行一个存储过程时，SQL SEVER查询优化器对其进行分析、优化，并保存其在内存中执行过的版本，以后可直接调用这个预编译过的版本，不需要再编译优化。而批处理的T-SQL语句每次运行都需要预编译和优化，所以执行速度会慢很多。
3、减轻网络流量
当在客户机上远程调用某存储过程时，只需在网络中传递该存储过程的调用语句，而不是该存储过程的SQL语句，从而减轻了网络流量，降低了网络负载。
4、具有良好的安全性
系统管理员可以限制数据库用户对不同存储过程的权限，从而实现对数据访问的限制，避免非授权用户对数据的访问，保证数据的安全。

存储过程和自定义函数比较

1、存储过程可以有输入参数、返回单个或多个返回值，也可以没有返回值，而函数可以有输入参数，必须有返回值，且能返回标量值或者表对象。
2、存储过程一般是作为一个独立的部分来执行，而函数可以被任意调用。标量函数可以当做表达式来使用，函数可以作为查询语句SELECT的一个部分来调用。

课上写的乱七八糟

create procedure pro_trans
@fromCardid int,
@inCardid int,
@money money
as 
begin
    begin transaction
        declare @err int;
        update bankCard set balance  = balance - @money where cardid = @fromCardid
        set @err = @@error
        update bankCard set balance = balance + @money where cardid = @inCardid
        set @err = @err + @@error
        if(@err > 0)
            begin
                rollback
                print '失败'
            end
        else
            begin 
                print '成功'
                commit
            end
end

alter table bankcard add constraint ck_balance check(balance >= 0)

select * from bankcard
execute pro_trans 10000004, 10000005, 100 
execute pro_trans 10000004, 10000005, 999999 


alter procedure pro_trans
@result varchar(200) output,
@fromCardid int,
@inCardid int,
@money money = 100
as 
begin
    begin transaction
        declare @err int;
        update bankCard set balance  = balance - @money where cardid = @fromCardid
        set @err = @@error
        update bankCard set balance = balance + @money where cardid = @inCardid
        set @err = @err + @@error
        if(@err > 0)
            begin
                rollback
                set @result = '失败'
            end
        else
            begin 
                set @result = '成功'
                commit
            end
end

--输出参数在后面
declare @result varchar(200);
execute pro_trans 10000004, 10000005, 100, @result --wrong
select @result 

declare @result varchar(200);
execute pro_trans 10000004, 10000005, 100, @result output --必须要加output
select @result 

declare @result varchar(200);
execute pro_trans @fromCardid = 10000005, @inCardid = 10000004, @money = 100, @result output  --error
select @result 

--输出参数在最前面
declare @result varchar(200);
execute pro_trans @result output, 10000004, 10000005, 100 --必须要加output
select @result 

declare @result varchar(200);
execute pro_trans @result output, @fromcardid = 10000004,@incardid =  10000005, @money = default --必须要加output
select @result 

declare @result varchar(200);
execute pro_trans @result output, @fromcardid = 10000004,@incardid =  10000005, @money = 200 --必须要加output
select @result 



select * from bankcard

sinEagle

SQL Server -触发器、游标和存储过程

触发器

触发器的分类

DML触发器

INSERTED表

DELETED表

DDL触发器

登录触发器

触发器的代码实现

创建一个触发器

课上写的乱七八糟

存储过程

定义

优点

存储过程和自定义函数比较

课上写的乱七八糟