创建触发器以将列值插入同一表的其他列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屋！