SPC0025 用 SPC0001 做模板是吧?不就是复制插入。
CREATE PROCEDURE createBOM
@PRDNO varchar(7),
@MARK varchar(10),
@RESULT int OUTPUT -- 0:成功,1:失败(没有做模板的BOM)
AS
DECLARE @tempBOM_ID varchar(7) SELECT TOP 1
@tempBOM_ID = BOM_ID
FROM MF_BOM
WHERE BOM_ID LIKE LEFT(@PRDNO,3)+'%' IF (@@ROWCOUNT = 0)
BEGIN
SET @RESULT = 1
RETURN
END INSERT INTO MF_BOM
SELECT @PRDNO+'->',
DEP,
QTY,
@MARK
FROM MF_BOM
WHERE BOM_ID = @tempBOM_ID INSERT INTO TF_BOM
SELECT @PRDNO+'->',
PRDNO,
PRDNAME,
QTY,
@MARK
FROM TF_BOM
WHERE BOM_ID = @tempBOM_ID SET @RESULT = 0
GO
CREATE PROCEDURE createBOM
@PRDNO varchar(7),
@MARK varchar(10),
@RESULT int OUTPUT -- 0:成功,1:失败(没有做模板的BOM)
AS
DECLARE @tempBOM_ID varchar(7) SELECT TOP 1
@tempBOM_ID = BOM_ID
FROM MF_BOM
WHERE BOM_ID LIKE LEFT(@PRDNO,3)+'%' IF (@@ROWCOUNT = 0)
BEGIN
SET @RESULT = 1
RETURN
END INSERT INTO MF_BOM
SELECT @PRDNO+'->',
DEP,
QTY,
@MARK
FROM MF_BOM
WHERE BOM_ID = @tempBOM_ID INSERT INTO TF_BOM
SELECT @PRDNO+'->',
PRDNO,
PRDNAME,
QTY,
@MARK
FROM TF_BOM
WHERE BOM_ID = @tempBOM_ID SET @RESULT = 0
GO
ON [dbo].[tf_pos]
FOR INSERT,DELETE,UPDATE
AS
SET NOCOUNT ON
--没有的插入数据 如MF_BOM没有现存的是否要产生表头
INSERT mf_bom(bom_id,qty,dep_no,rem)
SELECT prd_no+'->',1,'01','000'
FROM INSERTED I
WHERE NOT EXISTS(
SELECT 1
FROM mf_bom m
WHERE m.bom_id =i.prd_no+'->'
)
INSERT tf_bom(bom_id,prd_no,prd_name,qty,rem)
select i.prd_no+'->',a.prd_no,a.prd_name,a.qty,a.rem
from tf_bom a,INSERTED I where a.bom_id=SUBSTRING(i.prd_no,1,2)+'0001->'
and NOT EXISTS(
SELECT 1
FROM tf_bom m
WHERE m.bom_id =i.prd_no+'->'
2)先单独判断是否存在,存在就退出;没必要在INSERT 中判断。
3)SUBSTRING(i.prd_no,1,3),前缀是3位吧。