X
xrday
Unregistered / Unconfirmed
GUEST, unregistred user!
IF EXISTS (SELECT name FROM sysobjects WHERE name = 'gReportU' AND type = 'TR')
DROP TRIGGER gReportU
GO
/***********************************************
tReport表的触发器。审核Report表中的数据。
如果存在工作中心上报的日报数,就更新相关的工厂日历。
作者:proyer xu
日期:2002.06.24
***********************************************/
CREATE TRIGGER gReportU
ON tRecord
FOR UPDATE
AS
SET NOCOUNT ON
if @@rowcount<>1
begin
raiserror('无法接受的数据',16,1)
goto hndEnd
end
if not update(fState)
begin
goto hndEnd
end
if not ((select fState from deleted)='0' and (select fState from inserted)='1')
begin
goto hndEnd
end
Begin tran
----获取“流水号”、“工厂日历“,然后将Record中的数据更新到资源日历中。
--如果存在资源的情况,就更新资源使用情况tResCalendar
declare @datDay datetime --工厂日历
declare @intCellID int --流水ID
select @datDay=cast(fDay as char(10)),@intCellID=fCellID from inserted
--如果在tRecord表中存在数据,表示有工作中心及资源日报。
if exists(select 0 from tRecord where fDay=@datDay and fCellID=@intCellID)
begin
--将tRecord中存在,而tResCalendar中不存在的记录插入。
insert into tResCalendar(fDay,fCellID,fToolID,fPTime,fPQty,fFTime,fFQty)
select fDay,fCellID,fToolID,0,0,0,0 from tRecord
where cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) in
(select cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) from tRecord)
and cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) not in
(select cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) from tResCalendar)
--创建一个临时表保存汇总数据
select distinct fDay,fCellID,fToolID,sum(fTime) as sumTime,sum(fQty) as sumQty
into #dTempRecord from tRecord
where fDay=@datDay and fCellID=@intCellID group by fDay,fCellID,fToolID
--将汇总数据加入tResCalendar表中。
update tResCalendar
set tResCalendar.fFTime=tResCalendar.fFTime+sumTime
,tResCalendar.fFQty=tResCalendar.fFQty+sumQty
from tResCalendar,#dTempRecord where tResCalendar.fDay=#dTempRecord.fDay
and tResCalendar.fToolID=#dTempRecord.fToolID
and tResCalendar.fCellID=#dTempReocrd.fCellID
if @@error<>0
begin
Rollback Tran
end
else
begin
Commit Tran
end
hndEnd:
SET NOCOUNT OFF
DROP TRIGGER gReportU
GO
/***********************************************
tReport表的触发器。审核Report表中的数据。
如果存在工作中心上报的日报数,就更新相关的工厂日历。
作者:proyer xu
日期:2002.06.24
***********************************************/
CREATE TRIGGER gReportU
ON tRecord
FOR UPDATE
AS
SET NOCOUNT ON
if @@rowcount<>1
begin
raiserror('无法接受的数据',16,1)
goto hndEnd
end
if not update(fState)
begin
goto hndEnd
end
if not ((select fState from deleted)='0' and (select fState from inserted)='1')
begin
goto hndEnd
end
Begin tran
----获取“流水号”、“工厂日历“,然后将Record中的数据更新到资源日历中。
--如果存在资源的情况,就更新资源使用情况tResCalendar
declare @datDay datetime --工厂日历
declare @intCellID int --流水ID
select @datDay=cast(fDay as char(10)),@intCellID=fCellID from inserted
--如果在tRecord表中存在数据,表示有工作中心及资源日报。
if exists(select 0 from tRecord where fDay=@datDay and fCellID=@intCellID)
begin
--将tRecord中存在,而tResCalendar中不存在的记录插入。
insert into tResCalendar(fDay,fCellID,fToolID,fPTime,fPQty,fFTime,fFQty)
select fDay,fCellID,fToolID,0,0,0,0 from tRecord
where cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) in
(select cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) from tRecord)
and cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) not in
(select cast(fDay as char(10))+'|'+cast(fCellID as char(10))+'|'+cast(fToolID as char(10)) from tResCalendar)
--创建一个临时表保存汇总数据
select distinct fDay,fCellID,fToolID,sum(fTime) as sumTime,sum(fQty) as sumQty
into #dTempRecord from tRecord
where fDay=@datDay and fCellID=@intCellID group by fDay,fCellID,fToolID
--将汇总数据加入tResCalendar表中。
update tResCalendar
set tResCalendar.fFTime=tResCalendar.fFTime+sumTime
,tResCalendar.fFQty=tResCalendar.fFQty+sumQty
from tResCalendar,#dTempRecord where tResCalendar.fDay=#dTempRecord.fDay
and tResCalendar.fToolID=#dTempRecord.fToolID
and tResCalendar.fCellID=#dTempReocrd.fCellID
if @@error<>0
begin
Rollback Tran
end
else
begin
Commit Tran
end
hndEnd:
SET NOCOUNT OFF