Create trigger to insert a column value into other column of same table SQL Server 2005

sql, sql-server, sql-server-2005, triggers

Solution

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

I used this table:

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

Problem

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. Suppose I have table like below ``` 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 .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 value gets inserted into columnA ``` ColumnA | ColumnB xyz | xyz ``` But null value gets updated in both if some other application inserts value in columnA ``` insert into triggertestTable(columnA) values('pqr') ``` Now records are ``` ColumnA | ColumnB xyz | xyz NULL | NULL ``` The correct recordset should be like below ``` ColumnA | ColumnB xyz | xyz pqr | NULL ``` How to solve this problem.

Original source