SqlServer 2008 觸發器 INSTEAD OF UPDATE 更新欄位不固定-有解無憂

SqlServer 2008 觸發器 INSTEAD OF UPDATE 更新欄位不固定

更新表中欄位不固定如何寫觸發器

uj5u.com熱心網友回復：



Declare 欄位 varchar(255)

---欄位記得賦值

Update 表 Set 欄位

uj5u.com熱心網友回復：

能寫個完整的嗎？我update是某個欄位上，現在有個問題，更新其他欄位提示執行成功，但資料沒更新到

uj5u.com熱心網友回復：

CREATE TRIGGER TRIGER_YYDATA_INSERT
ON YYDATA
instead of UPDATE
AS
IF update (signno)
BEGIN
DECLARE @count INT
select @count = COUNT(1) from INSERTED A where A.SIGNNO in (select signno from YYDATA where LEFT(APPOINT_DATE,10) = LEFT(A.APPOINT_DATE,10))
IF @count > 0
begin
raiserror('SIGNNO REPEAT',16,1)
rollback transaction
END
ELSE
begin
UPDATE YYDATA SET
SIGNTIME = T2.SIGNTIME,
SIGNNO = T2.SIGNNO,
SIGNSTATE = T2.SIGNSTATE
FROM YYDATA AS t1, inserted AS t2 WHERE t1.APPOINT_ID = t2.APPOINT_ID
end
END

uj5u.com熱心網友回復：

參考 3 樓 JunZiLianJH 的回復:

CREATE TRIGGER TRIGER_YYDATA_INSERT
ON YYDATA
instead of UPDATE
AS
IF update (signno)
BEGIN
DECLARE @count INT
select @count = COUNT(1) from INSERTED A where A.SIGNNO in (select signno from YYDATA where LEFT(APPOINT_DATE,10) = LEFT(A.APPOINT_DATE,10))
IF @count > 0
begin
raiserror('SIGNNO REPEAT',16,1)
rollback transaction
END
ELSE
begin
UPDATE YYDATA SET
SIGNTIME = T2.SIGNTIME,
SIGNNO = T2.SIGNNO,
SIGNSTATE = T2.SIGNSTATE
FROM YYDATA AS t1, inserted AS t2 WHERE t1.APPOINT_ID = t2.APPOINT_ID
end
END

這是我的sql，但不更新signno的時候，更新沒效果

uj5u.com熱心網友回復：

參考 4 樓 JunZiLianJH 的回復:

Quote: 參考 3 樓 JunZiLianJH 的回復:

CREATE TRIGGER TRIGER_YYDATA_INSERT
ON YYDATA
instead of UPDATE
AS
IF update (signno)
BEGIN
DECLARE @count INT
select @count = COUNT(1) from INSERTED A where A.SIGNNO in (select signno from YYDATA where LEFT(APPOINT_DATE,10) = LEFT(A.APPOINT_DATE,10))
IF @count > 0
begin
raiserror('SIGNNO REPEAT',16,1)
rollback transaction
END
ELSE
begin
UPDATE YYDATA SET
SIGNTIME = T2.SIGNTIME,
SIGNNO = T2.SIGNNO,
SIGNSTATE = T2.SIGNSTATE
FROM YYDATA AS t1, inserted AS t2 WHERE t1.APPOINT_ID = t2.APPOINT_ID
end
END

這是我的sql，但不更新signno的時候，更新沒效果

IF update (signno) 你在這里限定了更新的欄位才觸發，如果想每次修改都觸發update 陳述句就把這條判斷洗掉。

uj5u.com熱心網友回復：

不是這個意思，我是想update陳述句中有 signno 欄位才觸發，update 中沒有這個欄位，按正常update執行即可

uj5u.com熱心網友回復：

你是不是BEGIN END寫錯了，試試下面的呢



REATE TRIGGER TRIGER_YYDATA_INSERT

ON YYDATA

instead of UPDATE

AS

IF update (signno)

BEGIN

DECLARE @count INT

select  @count = COUNT(1) from INSERTED A where A.SIGNNO in (select signno from YYDATA where LEFT(APPOINT_DATE,10) = LEFT(A.APPOINT_DATE,10))

    IF @count > 0

    begin 

    raiserror('SIGNNO REPEAT',16,1) 

    rollback transaction 

    end

END

ELSE

BEGIN 

 UPDATE YYDATA SET 

SIGNTIME = T2.SIGNTIME,

SIGNNO = T2.SIGNNO,

SIGNSTATE = T2.SIGNSTATE 

FROM YYDATA AS t1, inserted AS t2 WHERE t1.APPOINT_ID = t2.APPOINT_ID

END

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/26149.html

標籤：疑難問題

上一篇：[學習筆記] RabbitMQ的簡單使用

下一篇：安裝資料庫時候的問題