我有两张表Account(主表)Account_Day,我想将插入ACCOUNT表中数据按天(我截取Account时间的前十位),按类型(OperationType),将收入金额(Earning)和支出(Defray)统计起来放到Account_Day表中!
我的触发器是这么写的!错误了,谁指点下。
ALTER TRIGGER [dbo].[Tg_Account_Day] on [dbo].[Account]
for insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @StartTime datetime,
@Time_Day varchar(10),
@Earning numeric(18, 6),
@Defary numeric(18,6),
@OperationType varchar(50)
select @StartTime=StartTime(主表中记录时间的列) from Account
set @Time_Day=convert(varchar(10),@StartTime,120) select @Earning=sum(Earning) from Account
where convert(varchar(10),StartTime,120)=@Time_Day and OperationType=@OperationType select @Defary=sum(Defray) from Account
where convert(varchar(10),StartTime,120)=@Time_Day and OperationType=@OperationType if not exists(select 0 from Account_Day where [Time_Day]=@Time_Day)
begin
insert into Account_Day(
[Time_Day],
[Earning],
[Defary],
[OperationType])
values
(
@Time_Day,
@Earning,
@Defary,
@OperationType
)
end
-- Insert statements for trigger here
END
我的触发器是这么写的!错误了,谁指点下。
ALTER TRIGGER [dbo].[Tg_Account_Day] on [dbo].[Account]
for insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @StartTime datetime,
@Time_Day varchar(10),
@Earning numeric(18, 6),
@Defary numeric(18,6),
@OperationType varchar(50)
select @StartTime=StartTime(主表中记录时间的列) from Account
set @Time_Day=convert(varchar(10),@StartTime,120) select @Earning=sum(Earning) from Account
where convert(varchar(10),StartTime,120)=@Time_Day and OperationType=@OperationType select @Defary=sum(Defray) from Account
where convert(varchar(10),StartTime,120)=@Time_Day and OperationType=@OperationType if not exists(select 0 from Account_Day where [Time_Day]=@Time_Day)
begin
insert into Account_Day(
[Time_Day],
[Earning],
[Defary],
[OperationType])
values
(
@Time_Day,
@Earning,
@Defary,
@OperationType
)
end
-- Insert statements for trigger here
END
解决方案 »
- 这东西没人会吗?------Cookie
- Orcal 数据库中存长字符串
- WINFORM的问题
- C#.net 中验证控件:ValidationSummary显示信息为什么必须要在页面加一个Button的服务器控件后来点击这个Button才能显示啊?而其它的验证控件(如:RequiredFieldValidator)就不用提交页面就可以直接显示信息
- 简单文件读取问题
- 100分求《C#数据库编程从入门到精通》电子版图书
- 请教数据更改后如何刷新Grid中的显示数据
- 有办法获得 SQLServer 中的表结构 或者 字段的描述信息吗?
- 基于C#.net的论坛中一些功能实现问题求教
- DataSet读入Xml文件,然后使用DataTable的Select方法过滤,结果莫名其妙
- 删除Excel表问题
- DataGridView的RowLeave事件
ALTER TRIGGER [dbo].[Tg_Account_Day] on [dbo].[Account]
for insert
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;INSERT INTO Account_Day([Time_Day],[Earning],[Defary],[OperationType])
SELECT [Time_Day],[Earning],[Defary],[OperationType] FROM INSERTEDEND
没试过,不知道行不