作者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