我现在有一个客户信息表,在此表中每个客户一条信息,表中有现汇和承兑两个字段,另一个表是客户资金往来帐表,也对应有现汇和承兑两个字段,我现在想在后边表中加一触发器,后边表有记录增加、修改、删除时将对应客户的所有现汇和承兑汇总后修改前边表的现汇和承兑字段,我写了一个可是还是有点问题,请各位老师帮忙,谢谢!
测试表结构如下CREATE TABLE [Customer] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [nvarchar] (30),
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL ) CREATE TABLE [WangLaiZhang] (
[ids] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [char] (30) ,
[adddate] [datetime] NULL ,
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
测试表结构如下CREATE TABLE [Customer] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [nvarchar] (30),
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL ) CREATE TABLE [WangLaiZhang] (
[ids] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [char] (30) ,
[adddate] [datetime] NULL ,
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL
) ON [PRIMARY]
GO
解决方案 »
- sql合并问题
- 求SQL 语句!急急急.....
- 怎么查看sqlserve表中某个索引是否存在?
- 存储过程报错,请教大虾指点!急急急!
- 紧急求助 Update 错了 字段 错误更新了100万数据 救民阿 救民阿 救民阿 救民阿 救民阿
- sql代理执行网页程序,成功执行后,但job无法停止
- SQL 急 谁能给我讲下呀
- Sql server书籍下载,不要告诉空连接
- 我的触发器怎么触发不了啊!~~~~~~~~~~~~~!!!!!!!!!!!!!!!!!!
- 请问:将一个带两位小数的数据转换为大写金额的SQL语句如何书写
- 我觉得是一个疑难问题了。登录SQL 2008时报如下错误,跪求高手指点。
- 求sql语句
for insert
as
update c
set xianhui+t.xianhui,chengdui=chengdui+t.chengdui
from [Customer] c inner join(
select sum(xianhui)xianhui,sum(chengdui)chengdui
from inserted
group by [custname])t
where c.[custname]=t.[custname]
for delete
as
update c
set xianhui-t.xianhui,chengdui=chengdui-t.chengdui
from [Customer] c inner join(
select sum(xianhui)xianhui,sum(chengdui)chengdui
from deleted
group by [custname])t
where c.[custname]=t.[custname]
for update
as
update c
set xianhui+t.xianhui,chengdui=chengdui+t.chengdui
from [Customer] c inner join(
select sum(i.xianhui-d.xianhui)xianhui,sum(i.chengdui-d.chengdui)chengdui
from inserted i,deleted d
where i.[custname]=d.[custname]
group by [custname])t
where c.[custname]=t.[custname]
create trigger tri_insert on [WangLaiZhang]
for insert
as
update c
set xianhui=xianhui+t.xianhui,chengdui=chengdui+t.chengdui
from Customer c inner join(
select sum(xianhui)xianhui,sum(chengdui)chengdui
from inserted
group by custname) t
where c.custname=t.custname
还是报有语法错误,请老师再给测一下
把where 改成on
没测试环境
你测测看吧
CREATE TABLE [Customer] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [nvarchar] (30),
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL ) CREATE TABLE [WangLaiZhang] (
[ids] [int] IDENTITY (1, 1) NOT NULL ,
[custname] [char] (30) ,
[adddate] [datetime] NULL ,
[xianhui] [numeric](18, 2) NULL ,
[chengdui] [numeric](18, 2) NULL
) ON [PRIMARY]
GOinsert into customer(custname,xianhui,chengdui) select '王老板',0,0 union all select '李老板',0,0 insert into wanglaizhang(custname,xianhui,chengdui)
select '王老板',1000,2000 union all
select '李老板',8000,9200
服务器: 消息 207,级别 16,状态 3,过程 tri_update,行 4
列名 'i.custname' 无效。
服务器: 消息 207,级别 16,状态 1,过程 tri_update,行 4
列名 'i.custname' 无效。
服务器: 消息 209,级别 16,状态 1,过程 tri_update,行 4
列名 'custname' 不明确。
for update
as
update c
set c.xianhui+t.xianhui,c.chengdui=chengdui+t.chengdui
from [Customer] c inner join(
select sum(i.xianhui-d.xianhui)xianhui,sum(i.chengdui-d.chengdui)chengdui,custname
from inserted i,deleted d
where i.[custname]=d.[custname]
group by i.[custname])t
on c.[custname]=t.[custname]
for update
as
update c
set c.xianhui=c.xianhui+t.xianhui,c.chengdui=c.chengdui+t.chengdui
from [Customer] c inner join(
select sum(i.xianhui-d.xianhui)xianhui,sum(i.chengdui-d.chengdui)chengdui,custname
from inserted i,deleted d
where i.[custname]=d.[custname]
group by i.[custname])t
on c.[custname]=t.[custname]
CREATE TRIGGER [TRI_IN&UP] ON [dbo].[WangLaiZhang]
FOR INSERT, UPDATE
AS
DECLARE @xianhui decimal ,
@chengdui decimal,
@custname char(30)SELECT @xianhui =sum(e.xianhui),
@chengdui=sum(e.chengdui) ,
@custname=e.custname
FROM wanglaizhang e INNER JOIN inserted i ON e.custname = i.custname
JOIN customer j ON j.custname = i.custname group by e.custnameupdate customer set xianhui=@xianhui,chengdui=@chengdui where custname=@custname
CREATE TRIGGER [TRI_Del] ON [dbo].[WangLaiZhang]
FOR delete
AS
DECLARE @xianhui decimal ,
@chengdui decimal,
@custname char(30)SELECT @xianhui =sum(e.xianhui),
@chengdui=sum(e.chengdui) ,
@custname=e.custname
FROM wanglaizhang e INNER JOIN deleted i ON e.custname = i.custname
JOIN customer j ON j.custname = i.custname group by e.custnameupdate customer set xianhui=@xianhui,chengdui=@chengdui where custname=@custname