Create a trigger to insert a column value into another column in the same SQL Server 2005 table

Question

How to create a trigger to insert a value into a column in the same way as the value of another column of the same table of the row just inserted in the table.

Suppose I have a table as shown below

ColumnA | ColumnB 

I want the value of column B to be inserted into ColumnA as soon as the row is inserted into the table or the value of column B is updated. But this should not be the other way around, i.e. insert column value into column B

.Below code only handles INSERT, please help me how to handle how to Insert and update a table, i.e. when the value of column B is 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 the values ​​inserted as shown below

 insert into triggertestTable(columnB) values('xyz') 

The value of column B is inserted into columnA

 ColumnA | ColumnB xyz | xyz 

But the null value is updated in both cases if some other application inserts the value in column A

  insert into triggertestTable(columnA) values('pqr') 

Now records

 ColumnA | ColumnB xyz | xyz NULL | NULL 

The correct record set should be as below

  ColumnA | ColumnB xyz | xyz pqr | NULL 

How to solve this problem.

Answer 1

Try this trigger (it will copy the values ​​from column B to column A when the values ​​in column B are pasted or when the values ​​from ColumbB are updated):

 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 

Answer 2

inserted is a pseudo-table and may contain several rows. You must write something based on a set. Sort of:

  update triggertestTable set columnA=COALESCE(columnA,columnB) where id in (select id from inserted) 

(If triggertestTable has a primary key column called id )

The above values ​​are columnA to columnB if columnA does not yet have a value, which I think may be what you are looking for.

Answer 3

Have you tried the following:

  update triggertestTable set columnA=@value where columnB is not NULL 

Answer 4

In your trigger, you need to update ColumnA only if ColumnB is not NULL.

Use the following:

 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 @id1 int select @value=columnB from inserted if @value is not null begin update triggertestTable set columnA=@value end END GO