sql 觸發(fā)器 update的問(wèn)題
懸賞分：20|
CREATE TRIGGER GXDHSL ON RKD
FOR UPDATE
AS
DECLARE @DHDH VARCHAR(50) --計(jì)劃單號(hào)
DECLARE @SL decimal(18,6) --修改前數(shù)量
DECLARE @DHSL decimal(18,6) --修改后數(shù)量
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
go
但是RKD這張表在其它操作的時(shí)候還會(huì)更新 SHBZ和JZBZ這兩個(gè)字段，我想更新SHBZ和JZBZ的時(shí)候不觸發(fā)觸發(fā)器。不知道怎么寫(xiě)？
FOR UPDATE
AS
DECLARE @DHDH VARCHAR(50) --計(jì)劃單號(hào)
DECLARE @SL decimal(18,6) --修改前數(shù)量
DECLARE @DHSL decimal(18,6) --修改后數(shù)量
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
go
但是RKD這張表在其它操作的時(shí)候還會(huì)更新 SHBZ和JZBZ這兩個(gè)字段，我想更新SHBZ和JZBZ的時(shí)候不觸發(fā)觸發(fā)器。不知道怎么寫(xiě)？
使用更新什么字段才執(zhí)行觸發(fā)器就行了
CREATE TRIGGER GXDHSL ON RKD
FOR UPDATE
AS
IF(Update(字段名))
begin
DECLARE @DHDH VARCHAR(50) --計(jì)劃單號(hào)
DECLARE @SL decimal(18,6) --修改前數(shù)量
DECLARE @DHSL decimal(18,6) --修改后數(shù)量
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
end
go
CREATE TRIGGER GXDHSL ON RKD
FOR UPDATE
AS
IF(Update(字段名))
begin
DECLARE @DHDH VARCHAR(50) --計(jì)劃單號(hào)
DECLARE @SL decimal(18,6) --修改前數(shù)量
DECLARE @DHSL decimal(18,6) --修改后數(shù)量
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
end
go
好
不好其 他 回 答共3條
1樓
if @SL!=@DHSL
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
SELECT @DHDH=ysdh,@SL=SSSL FROM DELETED
SELECT @DHSL=SSSL FROM INSERTED
UPDATE GL_QGD SET DHSL=DHSL-ISNULL(@SL,0) WHERE DH=@DHDH
2樓
你這個(gè)表的操作只是對(duì)表的更新的時(shí)間有作用的，updete所對(duì)應(yīng)的表是inserted和deleted
我來(lái)回答這個(gè)問(wèn)題
立即登陸回答獲取會(huì)員積分，提高用戶級(jí)別