TA的每日心情 | 开心 昨天 08:43 |
---|
签到天数: 4037 天 [LV.Master]伴坛终老
|
楼主 |
发表于 2021-9-10 08:34:46
|
显示全部楼层
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tuihuo_sn_jiance] --触发器名称
ON [dbo].[DlySN] --触发器建立在哪张表上
AFTER INSERT,UPDATE --触发的条件:INSERT,DELETE,UPDATE
AS
--刚插入的数据保存在inserted表中,判断刚才插入的数据VchType,如果为“45"(退货),执行该触发器
if((select top 1 vchtype from inserted where RowNo = 1)='45')
BEGIN
SET NOCOUNT ON;
--建立临时表
SELECT * , ROW_NUMBER() OVER ( ORDER BY vchcode ) AS hanghao into TMP_DlySn FROM inserted
--建立临时变量
declare @i int,@INS_RowNo int,@INS_SN nvarchar(100)
set @i=1
select @INS_RowNo=(select COUNT(*) from inserted)
select @INS_SN=SN from inserted
while @i<=@INS_RowNo
BEGIN
if not exists (select * from DlySN where Vchtype = 11 and SN IN (select SN from TMP_DlySn where hanghao = @i))
BEGIN
RAISERROR ('请录入你对应销售过的产品序列号!',16,1)
ROLLBACK TRANSACTION
END
set @i=@i+1
END
drop table TMP_DlySn
SET NOCOUNT OFF
end |
|