sql插入触发器-插入前检测数据

/*alter TRIGGER trigger_test ON [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
FOR insert
AS
IF EXISTS(select * from inserted
where inserted.CBATCH not in (select CBATCH from [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS] ))
BEGIN
PRINT '已经在MES报检了，不允许在ERP报检'
ROLLBACK TRANSACTION
END
*/

/*
alter TRIGGER trigger_test ON [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
FOR insert
AS
declare @CBATCH_temp nvarchar(60)
declare @FCVOUCHERCODE_temp nvarchar(30)
select @CBATCH_temp=CBATCH
from inserted

select @FCVOUCHERCODE_temp=FCVOUCHERCODE
from [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
where CBATCH = @CBATCH_temp

 

IF EXISTS(select * from inserted,[UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
where inserted.CBATCH = [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS].CBATCH
)
BEGIN

PRINT 'FCVOUCHERCODE值是'+ @FCVOUCHERCODE_temp
if exists(select 1 from [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS] where @FCVOUCHERCODE_temp like 'M%')
BEGIN

PRINT '已经在MES报检了，不允许在ERP报检'
ROLLBACK TRANSACTION
END
else
BEGIN
PRINT '成功插入'
END
END
*/

alter TRIGGER trigger_test ON [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
FOR insert
AS
begin

/* 生产批号加订单号组成唯一id */
declare @CPROBATCH_temp nvarchar(60) /* 生产批号 */ /* 生产批号加订单号组成唯一id */
declare @CPROORDERCODE_temp nvarchar(30) /* 生产订单号 */
--declare @AUTOID_temp nvarchar(30) /* 原表中存在的autoid */
declare @count int /* 插入数据后表单符合条件的记录数 */

 

/* 报检单据号*/
declare @CBSYSBARCODE_temp nvarchar(80)

select @CPROBATCH_temp=CPROBATCH,@CPROORDERCODE_temp = CPROORDERCODE /* 在插入的数据中，提取生产订单号、批号 赋值给变量 */
from inserted

select @CBSYSBARCODE_temp=CBSYSBARCODE /* 根据生产订单号、批号 ，在原表中查询报检工单号、原表中存在的autoid*/
from [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
where CPROBATCH = @CPROBATCH_temp and CPROORDERCODE = @CPROORDERCODE_temp

declare @rows int
select @rows = isnull( COUNT (1) ,0) from QMINSPECTVOUCHERS where CPROBATCH = @CPROBATCH_temp and CPROORDERCODE = @CPROORDERCODE_temp and CBSYSBARCODE like '||QMCB|M%'

 

if(@rows>=1 )
begin
RAISERROR('已经在MES报检了，不允许在ERP报检',16,1)
return
end

--/* 若是在原表中找到对应生产订单号、批号的工单 */
--IF EXISTS(select * from inserted,[UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS]
-- where inserted.CPROBATCH = [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS].CPROBATCH and inserted.CPROORDERCODE = [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS].CPROORDERCODE
-- )
--BEGIN

-- --PRINT 'CBSYSBARCODE值是'+ @CBSYSBARCODE_temp
-- --PRINT 'CPROBATCH值是'+ @CPROBATCH_temp
-- --PRINT 'CPROORDERCODE值是'+ @CPROORDERCODE_temp
-- --PRINT 'AUTOID值是'+@AUTOID_temp

-- select @count = count(*) from
-- [UFDATA_103_2018].[dbo].[QMINSPECTVOUCHERS] B,inserted A
-- where B.CPROBATCH = @CPROBATCH_temp and B.CPROORDERCODE = @CPROORDERCODE_temp and B.CBSYSBARCODE like '||QMCB|M%'

-- --PRINT 'count值是'+@count
-- --print 'count值是' + cast(@count as varchar(50))

-- if (isnull( @count,0) > 1)
-- BEGIN
-- -- select 1
-- -- PRINT '已经在MES报检了，不允许在ERP报检'
-- -- --ROLLBACK TRANSACTION
-- RAISERROR('及格线必须在0~100之间',16,1)
-- return

-- END
-- -- else
-- -- BEGIN
-- -- PRINT '成功插入'
-- --select 2
-- -- END

END
go

 

 

 

 

值得注意的是 我之前用ROLLBACK TRANSACTION，但是这个情况用return 合适一些