触发器--游标--示例

create table bank
(
 cname nvarchar(10) primary key not null,
 cmoney money
)
go
insert into bank values('张三',1000);
insert into bank values('李四',1);
go
create table trans
(
 id int identity(1,1) primary key not null,
 cname nvarchar(10),
 ctype nchar(2),
 tmoney money
) ;

alter table bank add constraint ck_cmoney check(cmoney>=1);


insert into trans values('张三','支取',100);
update bank set cmoney=cmoney-100 where cname='张三';

select * from trans;
select * from bank;

//inserted deleted

insert into trans  
select '张三','支取',100 union
select '张三','支取',200

--------------------------------------------------------

drop trigger mytrans
go
create trigger mytrans on trans for insert
as
declare @cname nvarchar(10);
declare @ctype nchar(2);
declare @tmoney money;
declare mycursor cursor for
  select cname,ctype,tmoney from inserted;

open mycursor;
Fetch next from mycursor into @cname,@ctype,@tmoney;
WHILE @@FETCH_STATUS = 0
begin
 if(@ctype='支取')
 update bank set cmoney=cmoney-@tmoney where cname=@cname;
else
 update bank set cmoney=cmoney+@tmoney where cname=@cname;
 Fetch next from mycursor into @cname,@ctype,@tmoney;
end
CLOSE mycursor;
DEALLOCATE mycursor;
go