大家能帮我看一下这个触发器吗？如果我批量插人记录 就会出错
CREATE TRIGGER [Tr_DNFNO] ON [dbo].[DisNoteFit]
FOR INSERT
AS
IF (SELECT DNFNO FROM INSERTED) IS NULL OR (SELECT DNFNO FROM INSERTED) = 0
BEGIN
UPDATE A
SET DNFNO=(SELECT MAX(DNFNO)+1 FROM DisNoteFit
WHERE DNMNum=A.DNMNum AND DNCNum=A.DNCNum AND DNFNum <=A.DNFNum)
FROM DisNoteFit A JOIN INSERTED B on A.DNFNum=B.DNFNum
END
[解决办法]
批量插人?把那句判断去掉.然后加为空时,dnfno = 0
CREATE TRIGGER [Tr_DNFNO] ON [dbo].[DisNoteFit]
FOR INSERT
AS
BEGIN
UPDATE A
SET DNFNO=(SELECT isnull(MAX(DNFNO),0)+1 FROM DisNoteFit
WHERE DNMNum=A.DNMNum AND DNCNum=A.DNCNum AND DNFNum <=A.DNFNum)
FROM DisNoteFit A JOIN INSERTED B on A.DNFNum=B.DNFNum
END
[解决办法]
这段条件有问题.
WHERE DNMNum=A.DNMNum AND DNCNum=A.DNCNum AND DNFNum <=A.DNFNum)
FROM DisNoteFit A JOIN INSERTED B on A.DNFNum=B.DNFNum
[解决办法]
手工每次插入一条记录后检查通过上面的查询能否得到你需要的DNFNO
[解决办法]
钻石老大出手,应该没问题了.
[解决办法]
CREATE TRIGGER [Tr_DNFNO] ON [dbo].[DisNoteFit]
FOR INSERT
AS
BEGIN
DECLARE @DNFNum INT,@DNCNum INT,@I INT
DECLARE TC CURSOR FOR
SELECT DNFNum,DNCNum FROM INSERTED WHERE DNFNO IS NULL OR DNFNO=0
OPEN TC
FETCH NEXT FROM TC INTO @DNFNum,@DNCNum
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @I=ISNULL(MAX(DNFNO),0) FROM DisNoteFit WHERE DNMNum=@DNMNum AND DNCNum=@DNCNum
UPDATE DisNoteFit SET @I=@I+1,DNFNO=@I WHERE DNMNum=@DNMNum AND DNCNum=@DNCNum
FETCH NEXT FROM TC INTO @DNFNum,@DNCNum
END
CLOSE TC
DEALLOCATE TC
END
GO
