-- 涂布生產批號資料檢查
-- 作者:jacksheng 2007/03/02
CREATE TRIGGER [pRecord_2_2_CHECK] ON [dbo].[pRecord_2_2]
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
-- 定義變量
DECLARE @mId VARCHAR(5)
DECLARE @yymm VARCHAR(6)
DECLARE @pType VARCHAR(10)
DECLARE @pNumber VARCHAR(20)
DECLARE @ErrMsg VARCHAR(100)
-- 讀取變量
SELECT @mId = a.mId,@yymm = CONVERT(VARCHAR(6),a.dDate,112),
@pType = a.pType,@pNumber = RTRIM(b.pNumber)
FROM pRecord_2_1 a
INNER JOIN Inserted b
ON a.dNo = b.dNo
-- 檢查當前批號是否重複
IF (@pType IN('OPP','D/S'))
BEGIN
SELECT b.pNumber
FROM pRecord_2_1 a
INNER JOIN pRecord_2_2 b
ON a.dNo = b.dNo
WHERE b.pNumber = @pNumber OR
(a.mId = @mId AND CONVERT(VARCHAR(6),a.dDate,112) = @yymm
AND RIGHT(RTRIM(b.pNumber),4) = RIGHT(@pNumber,4)) --這個SELECT語句有問題,不過字段和參數都正確
IF (@@ROWCOUNT>1)
BEGIN
SELECT @ErrMsg = '批號:'+@pNumber+'已經存在!不允許輸入重複批號.'
RAISERROR(@ErrMsg,16,1)
END
END
ELSE
BEGIN
SELECT DISTINCT b.pNumber,a.dDate
FROM pRecord_2_1 a
INNER JOIN pRecord_2_2 b
ON a.dNo = b.dNo
WHERE b.pNumber = @pNumber
IF (@@ROWCOUNT>1)
BEGIN
SELECT @ErrMsg = '批號:'+@pNumber+'已經存在!不允許輸入重複批號.'
RAISERROR(@ErrMsg,16,1)
END
END
SET NOCOUNT OFF
-- 作者:jacksheng 2007/03/02
CREATE TRIGGER [pRecord_2_2_CHECK] ON [dbo].[pRecord_2_2]
FOR INSERT,UPDATE
AS
SET NOCOUNT ON
-- 定義變量
DECLARE @mId VARCHAR(5)
DECLARE @yymm VARCHAR(6)
DECLARE @pType VARCHAR(10)
DECLARE @pNumber VARCHAR(20)
DECLARE @ErrMsg VARCHAR(100)
-- 讀取變量
SELECT @mId = a.mId,@yymm = CONVERT(VARCHAR(6),a.dDate,112),
@pType = a.pType,@pNumber = RTRIM(b.pNumber)
FROM pRecord_2_1 a
INNER JOIN Inserted b
ON a.dNo = b.dNo
-- 檢查當前批號是否重複
IF (@pType IN('OPP','D/S'))
BEGIN
SELECT b.pNumber
FROM pRecord_2_1 a
INNER JOIN pRecord_2_2 b
ON a.dNo = b.dNo
WHERE b.pNumber = @pNumber OR
(a.mId = @mId AND CONVERT(VARCHAR(6),a.dDate,112) = @yymm
AND RIGHT(RTRIM(b.pNumber),4) = RIGHT(@pNumber,4)) --這個SELECT語句有問題,不過字段和參數都正確
IF (@@ROWCOUNT>1)
BEGIN
SELECT @ErrMsg = '批號:'+@pNumber+'已經存在!不允許輸入重複批號.'
RAISERROR(@ErrMsg,16,1)
END
END
ELSE
BEGIN
SELECT DISTINCT b.pNumber,a.dDate
FROM pRecord_2_1 a
INNER JOIN pRecord_2_2 b
ON a.dNo = b.dNo
WHERE b.pNumber = @pNumber
IF (@@ROWCOUNT>1)
BEGIN
SELECT @ErrMsg = '批號:'+@pNumber+'已經存在!不允許輸入重複批號.'
RAISERROR(@ErrMsg,16,1)
END
END
SET NOCOUNT OFF
if you another trigger update the table,without roll back,the action on another will do