创建触发器以将列值插入同一表的其他列SQL Server 2005 [英] Create trigger to insert a column value into other column of same table SQL Server 2005
问题描述
如何创建触发器以在与表中新插入的行的同一表的另一列的值相同的列中插入值。
How to create trigger to insert a value in a column same as value of another column of same table of a newly inserted row in the table.
假设我有如下表
ColumnA | ColumnB
我希望将行插入表或columnB值后立即将columnB值插入ColumnA得到更新。但这反之亦然,即将columnA值插入columnB
I want columnB value to be inserted into ColumnA as soon as row gets inserted into table or columnB value gets updated . But it should not be vice versa ie insert columnA value into columnB
。下面的代码仅处理INSERT,请帮助我如何同时处理 Insertion和Updation
.Below code handles INSERT only , please help me how to handle both Insertion and Updation of table ie when columnB value gets inserted or updated.
CREATE TRIGGER inserupdate
ON triggertestTable
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for trigger here
declare @value int
select @value=columnB from inserted
update triggertestTable
set columnA=@value
END
GO
如果将如下所示插入的值
This works fine if values inserted like below
insert into triggertestTable(columnB) values('xyz')
ColumnB值插入到columnA
ColumnB value gets inserted into columnA
ColumnA | ColumnB
xyz | xyz
但是如果某些其他应用程序在columnA中插入值,则两者的空值都会更新
But null value gets updated in both if some other application inserts value in columnA
insert into triggertestTable(columnA) values('pqr')
现在的记录是
ColumnA | ColumnB
xyz | xyz
NULL | NULL
正确的记录集应如下所示
The correct recordset should be like below
ColumnA | ColumnB
xyz | xyz
pqr | NULL
如何解决此问题。
推荐答案
尝试以下触发器(在将值插入ColumnB或更新ColumbB的值时,它将把ColumnB的值复制到ColumnA):
Try this trigger (it will copy the values from ColumnB to ColumnA when are inserted values into ColumnB or when are updated the values from ColumbB):
CREATE TRIGGER trgIU_triggertestTable_UpdateColumnAWhenColumnB
ON dbo.triggertestTable
AFTER INSERT,UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF UPDATE(ColumnB)
BEGIN
UPDATE dbo.triggertestTable
SET ColumnA=i.ColumnB
FROM inserted i
INNER JOIN dbo.triggertestTable t ON i.MyID=t.MyID
LEFT JOIN deleted d ON i.MyID=d.MyID
WHERE d.MyID IS NULL AND i.ColumnB IS NOT NULL -- Row was inserted
OR d.MyID IS NOT NULL -- Row was updated
END
END
GO
我使用了此表:
CREATE TABLE dbo.triggertestTable(
MyID INT IDENTITY(1,1) PRIMARY KEY, -- MyID should be a PRIMARY KEY or a mandatory(NOT NULL) UNIQUE constraint
ColumnA VARCHAR(100),
ColumnB VARCHAR(100)
);
GO
这篇关于创建触发器以将列值插入同一表的其他列SQL Server 2005的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!