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