15-07-22 数据库--存储过程、触发器

一、存储

在mydb数据库运行

select * from  fruit
select * from  Login
select * from  OrderDetails
select * from  Orders
select * from  info 
select * from  Family

go 
--存储过程，实现用户购买水果的操作，要判断购买数量和库存之间的关系，购买总价和余额之间的关系，如果购买成功修改4个表


create procedure buyfruit --创建一个存储
--定义参数,参数之间用逗号隔开
@uid varchar(50),--用户名
@fcode varchar(50),--要买水果编号
@sl int --要买的水果总量
as 
begin
    declare @kc int,@price float--定义变量名及数据类型、库存和单价
    select @kc=numbers,@price=price from Fruit where Ids = @fcode --查出库存和单价（变量赋值的第二种方法，查出数值进行赋值）
    --判断购买的数量是否大于库存
    if @sl>@kc
    begin
        print'库存不足！'
    end
    else--可以买
                   begin
                   declare @yue float --定义余额变量
                   select @yue = account from Login where UserName=@uid--对余额进行赋值
                   --判断余额是否大于购买的数量*单价
                    if @yue >= @price * @sl
                    begin--购买之后更改表信息
                    update Fruit set numbers = Numbers-@sl where Ids =@fcode--更改库存的数量-购买的数量
                    update Login set Account = Account - @price*@sl where UserName= @uid -- 更改存款金额：现有金额-购买数量*单价 
                    declare @sj int--定义一个变量随机
                    set @sj = CAST(RAND()*10000 as    int)--随机生成一个订单号，随机生成0-1之内的数
                    insert into Orders values (@sj,@uid,GETDATE())--getdate获取时间
                    insert into OrderDetails values(@sj,@fcode,@sl)   
                    end    
                    else--余额不足
                    begin
                           print'余额不足！'
                    end
            end
            
end

go
declare  @s int
exec @s = buyfruit 'zhangsan','k001',1 --调用存储过程，
print @s

--删除存储过程
drop proc BuyFriut

二、触发器

--触发器，特殊的存储过程，执行的时间和存储过程不一样，存储过程在调用的时候执行，触发器是在执行某种操作的时候触发执行，相当于C#里面的事件

触发器为特殊类型的存储过程，可在执行语言事件时自动生效。SQL Server 包括三种常规类型的触发器：DML 触发器、DDL 触发器和登录触发器。

当服务器或数据库中发生数据定义语言 (DDL) 事件时将调用 DDL 触发器。登录触发器将为响应 LOGON 事件而激发存储过程。与 SQL Server 实例建立用户会话时将引发此事件。

当数据库中发生数据操作语言 (DML) 事件时将调用 DML 触发器。DML 事件包括在指定表或视图中修改数据的 INSERT 语句、UPDATE 语句或 DELETE 语句。DML 触发器可以查询其他表，还可以包含复杂的 Transact-SQL 语句。将触发器和触发它的语句作为可在触发器内回滚的单个事务对待。如果检测到错误（例如，磁盘空间不足），则整个事务即自动回滚。

主要讲述DML触发器，DML触发器有两种：AFTER（FOR），INSTEAD OF触发器，同时DML 触发器使用 deleted 和 inserted 逻辑（概念）表。它们在结构上类似于定义了触发器的表，即对其尝试执行了用户操作的表。在 deleted 和 inserted 表保存了可能会被用户更改的行的旧值或新值。

对于INSERT 操作，inserted保留新增的记录，deleted无记录
对于DELETE 操作，inserted无记录，deleted保留被删除的记录
对于UPDATE操作，inserted保留修改后的记录，deleted保留修改前的记录

一、删除之后执行for(after) delete

--建立 Loginone、biandong数据库
select * from Loginone
select * from biandong
go
--创建触发器
create trigger TR_LOGINONE_DELETE--create trigger创建触发器
on Loginone --on后面是对那个表执行
for delete --删除的时候执行
--after delete --删除之后执行
--instead of delete --替代执行

as
begin
    declare @uid varchar(50),@name varchar(50)
    select @uid=UserName,@name=Name from deleted--从delect删除表中找2个数据，数据已经删除
    insert into biandong values(@uid,@name,'删除')
end
go
--在对表loginnoe进行删除的时候触发器执行
delete from Loginone where UserName='aaa'

二、替代执行instead of

--instead of触发器
--删除student里面的数据的时候用另外两条语句替代，先删从表再删主表

create trigger TR_STUDENT_DELETE
on Student 
instead of delete
as
begin
    declare @sno varchar(20)
    select @sno=sno from deleted
    delete from score where sno=@sno
    delete from student where sno=@sno
end
--执行删除的时候触发
delete from student where sno='101'
select * from student
select * from score
select * from course
select * from teacher