insert into repair(outcome,SN,Line,Defect1,defect2,station,AAction,RootCause,Comments,Defectpartsn,NewpartSn,ModuleName)
select outcomesdata.outcome,
products.serialnumber,
' ' as LineName,
max(case hashkey when 'observation' then hashvalue else '' end) observation,
max(case hashkey when 'symptom' then hashvalue else '' end) symptom, max(case hashkey when 'operation' then hashvalue else '' end) operation,
max(case hashkey when 'repair' then hashvalue else '' end) repair,
max(case hashkey when 'rootcause' then hashvalue else '' end) rootcause,
max(case hashkey when 'comments' then hashvalue else '' end) comments,
Max(case hashkey when 'Defective Part Number' then hashvalue else '' end )DefectivePart,
Max(case hashkey when 'New Part Number' then hashvalue else '' end )NewPart,
Max(case hashkey when 'Module Part Number' then hashvalue else '' end )ModulePart
from outcomesdata join outcomes on outcomesdata.outcome =outcomes.recno
join products on outcomes.product =products.recno
where outcomesdata.outcome =@id
group by outcome,serialnumber
order by serialnumber 直接 这 一句你给一个ID 能成功吗?
---insert into repair(outcome,SN,Line,Defect1,defect2,station,AAction,RootCause,Comments,Defectpartsn,NewpartSn,ModuleName)
select outcomesdata.outcome,
products.serialnumber,
max(' ') as LineName,
max(case hashkey when 'observation' then hashvalue else '' end) observation,
max(case hashkey when 'symptom' then hashvalue else '' end) symptom, max(case hashkey when 'operation' then hashvalue else '' end) operation,
max(case hashkey when 'repair' then hashvalue else '' end) repair,
max(case hashkey when 'rootcause' then hashvalue else '' end) rootcause,
max(case hashkey when 'comments' then hashvalue else '' end) comments,
Max(case hashkey when 'Defective Part Number' then hashvalue else '' end )DefectivePart,
Max(case hashkey when 'New Part Number' then hashvalue else '' end )NewPart,
Max(case hashkey when 'Module Part Number' then hashvalue else '' end )ModulePart
from outcomesdata join outcomes on outcomesdata.outcome =outcomes.recno
join products on outcomes.product =products.recno
where outcomesdata.outcome =@id
group by outcome,serialnumber
order by serialnumber
on outcomes
AFTER INSERT
as
declare @id int
select @id = Recno from inserted where operationtypename='Defective Log ' and passfail='1'
if @id is not null
begin insert into repair(outcome,SN,Line,Defect1,defect2,station,AAction,RootCause,Comments,Defectpartsn,NewpartSn,ModuleName)
select outcomesdata.outcome,
products.serialnumber,
' ' as LineName,
max(case when hashkey = 'observation' then hashvalue else '' end) observation,
max(case when hashkey = 'symptom' then hashvalue else '' end) symptom, max(case when hashkey = 'operation' then hashvalue else '' end) operation,
max(case when hashkey = 'repair' then hashvalue else '' end) repair,
max(case when hashkey = 'rootcause' then hashvalue else '' end) rootcause,
max(case when hashkey = 'comments' then hashvalue else '' end) comments,
Max(case when hashkey = 'Defective Part Number' then hashvalue else '' end )DefectivePart,
Max(case when hashkey = 'New Part Number' then hashvalue else '' end )NewPart,
Max(case when hashkey = 'Module Part Number' then hashvalue else '' end )ModulePart
from outcomesdata join outcomes on outcomesdata.outcome =outcomes.recno
join products on outcomes.product =products.recno
where outcomesdata.outcome =@id
group by outcome,serialnumber
order by serialnumber
end OK??
select @id = Recno from inserted where operationtypename='Defective Log ' and passfail='1'
if @id is not null --查询是否成立和结果集
outcomesdata join outcomes on outcomesdata.outcome =outcomes.recno
--
没有插入数据的原因是:
执行这个触发器时,另一个表的数据还没有插入.所以没有找到数据.
另外想问一下高人.因为outcomes,与outcomesdata的数据几乎是同时插入,时间上显示是同一时间插入,但通过分析outcomes中的数据插入时触发该触发器时.outcomesdata还没插入此条数据.想问一下,里面是否可以加上延迟的函数,等outcomesdata里插入数据后,再运行执么插入的动作.