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.

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.

Answer

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