Create trigger to insert a column value into other column of same table SQL Server 2005
Solution 1
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
Solution 2
inserted
is a pseudo-table and may contain multiple rows. You should write something set-based. Something like:
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 sets columnA
to columnB
's value, if columnA
doesn't already have a value, which I think might be what you're looking for.
Solution 3
In your trigger, you have 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
Mudassir Hasan
Updated on July 09, 2022Comments
-
Mudassir Hasan almost 2 years
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.