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
sql触发器的使用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].CPROBATC
H 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 合适⼀些