有四个表:入库主表,(RKID)入库明细表(RKMXID,RKPARENTID),出库主表(CKID),出库明细表(CKMXID,CKPARENTID,RKID,RKMXID),关系:RKID=RKPARENTID,CKID=CKPARENTID业务规则:出库根据入库的明细表生成,出库明细表里保存了入库的主、细表的主键,现在建一触发器来控制入库表的状态入库表的LOCK:IF 该单的入库明细表里所有行的出库数量和大于0,LOCK=1,ELSE LOCK=0我在出库明细表新建一触发器,代码:CREATE trigger T_CKMX
ON CK_CKMXB
AFTER UPDATE,INSERT,DELETE
AS
DECLARE
@RKID VARCHAR(20),--入库主键
@HEETTYPE NVARCHAR(20),
@SUMCOUNT DECIMAL(18,3),--入库明细表里的已出库数量求和
@SUMNO INT,--共几行
@FINISHSUM DECIMAL(18,3)--入库明细表的已出库数量
--定义光标,遍历插入或更新的数据
SET @SUMNO=(SELECT COUNT(*) FROM INSERTED)
IF @SUMNO>0
BEGIN
DECLARE C_FK SCROLL CURSOR FOR (SELECT RKID FROM INSERTED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM INSERTED)
END
ELSE
BEGIN
SET @SUMNO=(SELECT COUNT(*) FROM DELETED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM DELETED)
DECLARE C_FK SCROLL CURSOR FOR (SELECTRKID FROM DELETED) END
OPEN C_FK
FETCH FROM C_FK INTO @CKID,@CKMXID
IF @HEETTYPE='采购出库' BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SUMCOUNT=(SELECT SUM(OUTCOUNT) FROM CK_RKMXB WHERE RKPARENTID=@RKID)
IF @SUMCOUNT IS NULL SET @SUMCOUNT=0
IF @SUMCOUNT>0
BEGIN
UPDATE CK_RKZB SET LOCKED=1 WHERE RKID =@CKID ELSE
BEGIN
UPDATE CK_RKZB SET LOCKED=0 WHERE RKID =@CKID
END
FETCH FROM C_FK INTO @RKID
END
END
CLOSE C_FK
DEALLOCATE C_FK在使用时,发现新增数据的时候,LOCK不能改变,但在删除或修改出库明细表的时候,LOCK可以改变,请高手帮忙看看,谢谢!
ON CK_CKMXB
AFTER UPDATE,INSERT,DELETE
AS
DECLARE
@RKID VARCHAR(20),--入库主键
@HEETTYPE NVARCHAR(20),
@SUMCOUNT DECIMAL(18,3),--入库明细表里的已出库数量求和
@SUMNO INT,--共几行
@FINISHSUM DECIMAL(18,3)--入库明细表的已出库数量
--定义光标,遍历插入或更新的数据
SET @SUMNO=(SELECT COUNT(*) FROM INSERTED)
IF @SUMNO>0
BEGIN
DECLARE C_FK SCROLL CURSOR FOR (SELECT RKID FROM INSERTED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM INSERTED)
END
ELSE
BEGIN
SET @SUMNO=(SELECT COUNT(*) FROM DELETED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM DELETED)
DECLARE C_FK SCROLL CURSOR FOR (SELECTRKID FROM DELETED) END
OPEN C_FK
FETCH FROM C_FK INTO @CKID,@CKMXID
IF @HEETTYPE='采购出库' BEGIN
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SUMCOUNT=(SELECT SUM(OUTCOUNT) FROM CK_RKMXB WHERE RKPARENTID=@RKID)
IF @SUMCOUNT IS NULL SET @SUMCOUNT=0
IF @SUMCOUNT>0
BEGIN
UPDATE CK_RKZB SET LOCKED=1 WHERE RKID =@CKID ELSE
BEGIN
UPDATE CK_RKZB SET LOCKED=0 WHERE RKID =@CKID
END
FETCH FROM C_FK INTO @RKID
END
END
CLOSE C_FK
DEALLOCATE C_FK在使用时,发现新增数据的时候,LOCK不能改变,但在删除或修改出库明细表的时候,LOCK可以改变,请高手帮忙看看,谢谢!
SET @SUMNO=(SELECT COUNT(*) FROM INSERTED)
IF @SUMNO >0
BEGIN
DECLARE C_FK SCROLL CURSOR FOR (SELECT RKID FROM INSERTED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM INSERTED)
END
ELSE
BEGIN
...
END
for update时deleted,inserted都会存在..楼主的判断时不准确
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CK_CKMXB]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CK_CKMXB]
GOCREATE TABLE [dbo].[CK_CKMXB] (
[NBID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[PARENTNBID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[HPMXID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SHEETTYPE] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SUBID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[SUBLISTNBID] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[PRICE] [decimal](18, 3) NULL ,
[CESS] [decimal](18, 3) NULL ,
[OUTCOUNT] [decimal](18, 3) NULL ,
[OUTMONEY] [decimal](18, 2) NULL ,
[BOXCOUNT] [decimal](18, 0) NULL ,
[LOCATION] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[FINISH] [bit] NULL ,
[POSTIL] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[REMARK] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
触发器:
CREATE trigger T_CKMX
ON CK_CKMXB
AFTER INSERT,UPDATE,DELETE
AS
DECLARE
@SUBLISTNBID VARCHAR(20),
@PPNBID VARCHAR(20),
@HEETTYPE NVARCHAR(20),
@SUMCOUNT DECIMAL(18,3),
@SUMNO INT,--共几行
@FINISHSUM DECIMAL(18,3)
--定义光标,遍历插入或更新的数据
SET @SUMNO=(SELECT COUNT(*) FROM INSERTED)
IF @SUMNO>0
BEGIN
DECLARE C_FK SCROLL CURSOR FOR (SELECT SUBID,SUBLISTNBID FROM INSERTED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM INSERTED)
END
ELSE
BEGIN
SET @SUMNO=(SELECT COUNT(*) FROM DELETED)
SET @HEETTYPE=(SELECT TOP 1 SHEETTYPE FROM DELETED)
DECLARE C_FK SCROLL CURSOR FOR (SELECT SUBID,SUBLISTNBID FROM DELETED)
END
OPEN C_FK
FETCH FROM C_FK INTO @SUBLISTNBID,@PPNBID
WHILE @@FETCH_STATUS = 0
IF @HEETTYPE='采购出库' BEGIN
BEGIN
SET @SUMCOUNT=(SELECT SUM(OUTCOUNT) FROM CK_RKMXB WHERE PARENTNBID=@PPNBID)
IF @SUMCOUNT IS NULL SET @SUMCOUNT=0
IF @SUMCOUNT>0
BEGIN
UPDATE CK_RKZB SET LOCKED=1--(CASE WHEN @SUMCOUNT>0 THEN 0 ELSE 1 END)
WHERE NBID =@PPNBID
END
ELSE
BEGIN
UPDATE CK_RKZB SET LOCKED=0--(CASE WHEN @SUMCOUNT>0 THEN 0 ELSE 1 END)
WHERE NBID =@PPNBID
END
END
FETCH FROM C_FK INTO @SUBLISTNBID,@PPNBID
END
CLOSE C_FK
DEALLOCATE C_FK