Re: [SQL ] trigger 如何判斷欄位值

[daniel1205 (艾琳是我最愛的人!!)]

寫個小 Sample 給你參考 ...
直接丟下去執行就好了!

---------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]')
and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table2]
GO

CREATE TABLE [dbo].[Table1] (
  [C1] [varchar] (10) NULL ,
  [C2] [varchar] (10) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Table2] (
  [TRIGGER_INSERT] [varchar] (50) NULL
) ON [PRIMARY]
GO


CREATE TRIGGER [trg_for_TABLE1_UPDATE] ON [dbo].[Table1]
AFTER UPDATE
AS
IF UPDATE (C2)
BEGIN
    DECLARE @strOldData varchar(20)
    DECLARE @strNewData varchar(20)
    SET @strOldData = (SELECT C2 FROM DELETED)
    SET @strNewData = (SELECT C2 FROM INSERTED)

   IF @strNewData = 'A'
   BEGIN
      INSERT INTO Table2 VALUES ('UPDATE ''' + @strOldData + ''' TO ''' +
@strNewData + '''')
   END

END



--- 測試
INSERT INTO Table1 VALUES ('X1','XX2')
INSERT INTO Table1 VALUES ('X2','XX3')
INSERT INTO Table1 VALUES ('X3','XX4')
UPDATE Table1 SET C2 = 'B' WHERE C1 = 'X1'
UPDATE Table1 SET C2 = 'A' WHERE C1 = 'X1'
UPDATE Table1 SET C2 = 'A' WHERE C1 = 'X3'


--
※ 發信站: 批踢踢實業坊(ptt.cc) 
◆ From: 118.168.128.107
推 dedek:感謝D大~想問一下這句後面插入的值update是什麼意思?
推 dedek: ('UPDATE ''' + @strOldData + ''' TO '''
→ daniel1205:那個只是寫個字串到Table2, 驗證功能用的不用理他 XD
推 dedek:試了一下,雖然沒有跑出我要的效果,但是大概有方向,謝謝D大
→ daniel1205:重點就是黃色的那兩句啊!! XDD
推 dedek:沒錯,我後來再用 if @num=1 insert xxx values(xxx)