三个表:C_Card_Name ,C_Card_Worth ,C_Card_list
C_Card_list 是卡信息文件,包括了面值(worth对应C_Card_Worth的worth,int),卡名称(card 对应表C_Card_Name的id)
需要在C_Card_list表中建立触发器,计算C_Card_list表中不同面值、不同卡名的面值和 C_Card_Name id1 Card_Name MZ50 MZ100 MZ200
1 联华卡 50元面值求和 100元面值求和 200元面值求和 (触发器更新数据)
2 加油卡 50元面值求和 100元面值求和 200元面值求和 C_Card_Worth id2 Card_Worth
1 50
2 100
3 200
C_Card_list id3 Card_Worth Cardid
1 50 1
2 50 2
3 100 1
4 100 2
5 200 1
6 200 2
asp页面程序文件如下(测试通过):
<%
set rs=server.CreateObject("ADODB.RecordSet")
sql="select id from C_Card_Name"
rs.open sql,conn,1,1
do while not rs.eof
set rs1=server.CreateObject("ADODB.RecordSet")
sql="select WORTH from C_Card_Worth"
Rs1.Open Sql,Conn,1,1
do while not rs1.eof
conn.execute("update C_Card_Name set MZ"&rs1("worth")&" =(SELECT COUNT(*) FROM C_Card_list WHERE WORTH="&rs1("worth")&" and cardid="&rs("id1")&") where id3="&rs("id1")&" ")
rs1.movenext
loop
rs.movenext
loop
rs1.close
set rs1 = nothing
rs.close
set rs = nothing
set conn = nothing
%>
C_Card_list 是卡信息文件,包括了面值(worth对应C_Card_Worth的worth,int),卡名称(card 对应表C_Card_Name的id)
需要在C_Card_list表中建立触发器,计算C_Card_list表中不同面值、不同卡名的面值和 C_Card_Name id1 Card_Name MZ50 MZ100 MZ200
1 联华卡 50元面值求和 100元面值求和 200元面值求和 (触发器更新数据)
2 加油卡 50元面值求和 100元面值求和 200元面值求和 C_Card_Worth id2 Card_Worth
1 50
2 100
3 200
C_Card_list id3 Card_Worth Cardid
1 50 1
2 50 2
3 100 1
4 100 2
5 200 1
6 200 2
asp页面程序文件如下(测试通过):
<%
set rs=server.CreateObject("ADODB.RecordSet")
sql="select id from C_Card_Name"
rs.open sql,conn,1,1
do while not rs.eof
set rs1=server.CreateObject("ADODB.RecordSet")
sql="select WORTH from C_Card_Worth"
Rs1.Open Sql,Conn,1,1
do while not rs1.eof
conn.execute("update C_Card_Name set MZ"&rs1("worth")&" =(SELECT COUNT(*) FROM C_Card_list WHERE WORTH="&rs1("worth")&" and cardid="&rs("id1")&") where id3="&rs("id1")&" ")
rs1.movenext
loop
rs.movenext
loop
rs1.close
set rs1 = nothing
rs.close
set rs = nothing
set conn = nothing
%>
INSERT C_Card_Worth
SELECT 1 , 50 UNION
SELECT 2 , 100 UNION
SELECT 3 , 200 CREATE TABLE C_Card_list( id3 INT, Card_Worth INT, Cardid INT)
INSERT C_Card_list
SELECT 1 , 50 , 1 UNION
SELECT 2 , 50 , 2 UNION
SELECT 3 , 100 , 1 UNION
SELECT 4 , 100 , 2 UNION
SELECT 5 , 200 , 1 UNION
SELECT 6 , 200 , 2
SELECT * FROM C_Card_Worth
SELECT * FROM C_Card_listSELECT Cardid,
SUM(CASE WHEN Card_Worth=50 THEN Card_Worth ELSE 0 END)AS MZ50,
SUM(CASE WHEN Card_Worth=100 THEN Card_Worth ELSE 0 END)AS MZ100,
SUM(CASE WHEN Card_Worth=200 THEN Card_Worth ELSE 0 END)AS MZ200
FROM
C_Card_listGROUP BY Cardid
Cardid MZ50 MZ100 MZ200
----------- ----------- ----------- -----------
1 50 100 200
2 50 100 200(所影响的行数为 2 行)
楼主的触发器应该建立在第二个表上,根据第二个再更新第三个,再根据第三个表求出第一个表,
没弄懂,
将C_Card_list 中面值相同 卡名称相同的计算和 再更新到C_Card_Name 中不同对应的面值列中CREATE TRIGGER [Card_KC] ON [dbo].[C_Card_List]
FOR INSERT, UPDATE, DELETE
AS
--循环1 取出C_Card_Name 中的 @id1
--循环2 再取出C_Card_Worth的面值 @worthUPDATE C_Card_Name SET MZ@worth
= (SELECT COUNT(*) FROM C_Card_list WHERE Card = @id1) where id=@id1
结束循环2
结束循环1大概意思就是这样asp中我已经测试通过,可触发器不会 大家帮帮谢谢
将C_Card_list 中面值相同 卡名称相同的计算和 再更新到C_Card_Name 中不同对应的面值列中 CREATE TRIGGER [Card_KC] ON [dbo].[C_Card_List]
FOR INSERT, UPDATE, DELETE
AS
--循环1 取出C_Card_Name 中的 @id1
--循环2 再取出C_Card_Worth的面值 @worth UPDATE C_Card_Name SET MZ@worth
= (SELECT COUNT(*) FROM C_Card_list WHERE worth=@worth and Card = @id1) where id=@id1
结束循环2
结束循环1 大概意思就是这样 asp中我已经测试通过,可触发器不会 大家帮帮谢谢
create trigger listup
on C_Card_list
for insert,update,delete
as
BEGIN
declare @T_Card_Worth int
declare @T_Cardid int
select @T_Card_Worth=Card_Worth,@T_Cardid=Cardid
from insertedif (@T_Card_Worth=50)
begin
update C_Card_Name set mz50=(select (mz50+@T_Card_Worth) as mz50 from C_Card_Name where id1=@T_Cardid)
where id1=@T_Cardid
end
if (@T_Card_Worth=100)
begin
update C_Card_Name set mz100=(select (mz100+@T_Card_Worth) as mz100 from C_Card_Name where id1=@T_Cardid)
where id1=@T_Cardid
end
if (@T_Card_Worth=200)
begin
update C_Card_Name set mz200=(select (mz200+@T_Card_Worth) as mz200 from C_Card_Name where id1=@T_Cardid)
where id1=@T_Cardid
end
end;
update C_Card_Name set MZ50=MZ50+50 where id1=1 and MZ50=50 (如果id=1 Card_Worth=50的话)
create trigger listup
on C_Card_list
for insert
as
BEGIN
declare @T_Card_Worth int
declare @T_Cardid int
declare @mz varchar(20)
declare @sql varchar(8000)select @T_Card_Worth=Card_Worth,@T_Cardid=Cardid
from insertedset @mz='mz'+convert(varchar(10),@T_Card_Worth)
print(@mz)set @sql='update C_Card_Name set '+@mz+'=(select ('+@mz+'+'+convert(varchar(10),@T_Card_Worth)+') from C_Card_Name where id1='+convert(varchar(10),@T_Cardid)+') where id1='+convert(varchar(10),@T_Cardid)
exec(@sql)
end;
AS
if UPDATE(SX)
begin
......end
所以需要循环 另外怎么没看到求和的sum?
谢谢 再帮帮
最后的代码调试成功 给分!