现在我有两张表,一张是cardinfo 一张是transinfocreate table cardinfo --卡号表
(
cardid int identity(1,1), --卡号ID
balance money not null --余额
)
go
create table transinfo --交易表
(
transDate datetime not null, --交易时间
cardID int not null, --交易的卡号
tranType char(4) not null, --交易的类型(支出/存入)
transMoney money not null --交易的金额
)
我需要创建一个触发器,就是修改cardinfo表里面的balance列的时候,自动在transinfo表里面插入一条信息,如果是减去金额的话,类型就为支出,反则是存入,请各位高手们指点一下
(
cardid int identity(1,1), --卡号ID
balance money not null --余额
)
go
create table transinfo --交易表
(
transDate datetime not null, --交易时间
cardID int not null, --交易的卡号
tranType char(4) not null, --交易的类型(支出/存入)
transMoney money not null --交易的金额
)
我需要创建一个触发器,就是修改cardinfo表里面的balance列的时候,自动在transinfo表里面插入一条信息,如果是减去金额的话,类型就为支出,反则是存入,请各位高手们指点一下
as
begin
decimal @balance1 money
decimal @balance2 money
select @balance1 from deleted
select @balance2 from inseted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1
end--以下为金额变动为0时,不写入transinfo表
create trigger my_trig on cardinfo for update
as
begin
decimal @balance1 money
decimal @balance2 money
select @balance1 from deleted
select @balance2 from inseted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2
if @balance1 < @balance2
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1
end
为什么会报错对象名 'cardid' 无效。
那个 insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2
中的cardid是怎么来的哦,这个我比较不懂
as
begin
decimal @balance1 money
decimal @balance2 money
select @balance1 from deleted
select @balance2 from inseted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1
from inserted
endcreate trigger my_trig on cardinfo for update
as
begin
decimal @balance1 money
decimal @balance2 money
select @balance1 from deleted
select @balance2 from inseted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
if @balance1 < @balance2
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1 from inserted
end
select @balance2 from inserted
drop trigger my_trig
go
create trigger my_trig
on cardinfo
for update
as
declare @balance1 money
declare @balance2 money
select @balance1 = balance from deleted
select @balance2 = balance from inserted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1 from inserted
update cardinfo set balance = balance + 100 where cardid = '1010 3576 1212 1134'
我这样写,他会报错消息 217,级别 16,状态 1,过程 triger_transinfo,第 12 行
超出了存储过程、函数、触发器或视图的最大嵌套层数(最大层数为 32)。
drop trigger my_trig
go
create trigger my_trig
on cardinfo
for update
as
declare @balance1 money
declare @balance2 money
select @balance1 = balance from deleted
select @balance2 = balance from inserted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1 from inserted
go --这里update cardinfo set balance = balance + 100 where cardid = '1010 3576 1212 1134'
(
cardid varchar(20), --卡号ID
balance money not null --余额
)
go
create table transinfo --交易表
(
transDate datetime not null, --交易时间
cardID int not null, --交易的卡号
tranType char(4) not null, --交易的类型(支出/存入)
transMoney money not null --交易的金额
)
go
insert into cardinfo values('1',1)
gocreate trigger my_trig on cardinfo for update
as
begin
declare @balance1 money
declare @balance2 money
set @balance1 = 0
set @balance2 = 0
select @balance1 = balance from deleted
select @balance2 = balance from inserted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1 from inserted
end
go
update cardinfo set balance = balance + 1 where cardid = '1'
update cardinfo set balance = balance + 2 where cardid = '1'goselect * from transinfo
select * from cardinfodrop table cardinfo , transinfo/*
transDate cardID tranType transMoney
------------------------------------------------------ ----------- -------- ---------------------
2009-12-05 21:36:54.827 1 存入 1.0000
2009-12-05 21:36:54.827 1 存入 2.0000(所影响的行数为 2 行)cardid balance
-------------------- ---------------------
1 4.0000(所影响的行数为 1 行)
*/
(
cardid varchar(20), --卡号ID
balance money not null --余额
)
go
create table transinfo --交易表
(
transDate datetime not null, --交易时间
cardID int not null, --交易的卡号
tranType char(4) not null, --交易的类型(支出/存入)
transMoney money not null --交易的金额
)
go
insert into cardinfo values('1',1)
gocreate trigger my_trig on cardinfo for update
as
begin
declare @balance1 money
declare @balance2 money
set @balance1 = 0
set @balance2 = 0
select @balance1 = balance from deleted
select @balance2 = balance from inserted
if @balance1 > @balance2
insert into transinfo select getdate() , cardid , '支出' , @balance1 - @balance2 from inserted
else
insert into transinfo select getdate() , cardid , '存入' , @balance2 - @balance1 from inserted
end
go
update cardinfo set balance = balance + 10 where cardid = '1'
update cardinfo set balance = balance + 20 where cardid = '1'
update cardinfo set balance = balance - 5 where cardid = '1'
update cardinfo set balance = balance - 6 where cardid = '1'goselect * from transinfo
select * from cardinfodrop table cardinfo , transinfo/*
transDate cardID tranType transMoney
------------------------------------------------------ ----------- -------- ---------------------
2009-12-05 21:40:03.827 1 存入 10.0000
2009-12-05 21:40:03.843 1 存入 20.0000
2009-12-05 21:40:03.843 1 支出 5.0000
2009-12-05 21:40:03.843 1 支出 6.0000(所影响的行数为 4 行)
*/