作者daniel1205 (艾琳是我最愛的人!!)
看板Database
標題Re: [SQL ] trigger 如何判斷欄位值
時間Mon Jan 4 20:37:20 2010
寫個小 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 = 'X
3'
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 118.168.128.107
1F：推 dedek:感謝D大~想問一下這句後面插入的值update是什麼意思? 01/05 21:23
2F：推 dedek: ('UPDATE ''' + @strOldData + ''' TO ''' 01/05 21:41
3F：→ daniel1205:那個只是寫個字串到Table2, 驗證功能用的不用理他 XD 01/05 23:45
4F：推 dedek:試了一下,雖然沒有跑出我要的效果,但是大概有方向,謝謝D大 01/06 20:47
5F：→ daniel1205:重點就是黃色的那兩句啊!! XDD 01/07 01:04
6F：推 dedek:沒錯,我後來再用 if @num=1 insert xxx values(xxx) 01/08 18:12