22,199
社区成员
发帖
与我相关
我的任务
分享
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER trigger [dbo].[CDefect]
on [dbo].[OutcomesData]
after insert
as
declare @id int
declare @id2 int
select @id=outcome from inserted where hashkey='Observation'
if @id is not null
begin
select @id2=Recno from outcomes with (UPDLOCK) where operationtypename='Defective Log ' and passfail='1' and recno=@id
if @id2 is not null
begin
insert into trepair(outcome,TLI,SN,OperationID,Operation,Defect1,Commets1,defect2,AAction,RootCause,DefectPartsn,NewPartsn,ModuleName,Components,commets2,Line)
select outcomes.recno,
Products.TLI,
Products.SerialNumber,
max(case hashkey when 'OperatorID' then hashvalue else '' end) OperatorID,
max(case hashkey when 'WhereFound' then hashvalue else '' end) Operation,
max(case hashkey when 'observation' then hashvalue else '' end) observation,
max(case hashkey when 'Comments' then hashvalue else '' end) Commets1,
max(case hashkey when 'Symptom' then hashvalue else '' end) Symptom,
''as AAction,
''as RootCause,
''as DefectPartsn,
''as NewPartsn,
''as ModuleName,
''as Components,
''as Comments2,
Products.line
from outcomesdata with (UPDLOCK)join outcomes with (UPDLOCK) on outcomesdata.outcome =outcomes.recno
join products with (UPDLOCK) on outcomes.product =products.recno
where outcomesdata.outcome =@id
group by outcomes.recno,serialnumber,tli,line
end
end
max(case hashkey when 'Comments' then hashvalue else '' end) Commets1
, 没有值????