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

看板Database作者daniel1205 (艾琳是我最愛的人!!)時間16年前 (2010/01/04 20:37)推噓4(4推 0噓 2→)

留言6則, 2人參與討論串2/2 (看更多)

寫個小 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

01/05 21:23, , 1^F

01/05 21:23, 1^F

推

dedek

01/05 21:41, , 2^F

01/05 21:41, 2^F

→

daniel1205

01/05 23:45, , 3^F

01/05 23:45, 3^F