Avatar of Cobra967
Cobra967
Flag for United States of America asked on

Trigget to update a field from the same table

Hello, I am new to trigger and I need help to create a trigger that will copy the content from ColumnA and ColumnB into ColumnC. The trigger will fire when Data in ColumnA or ColumnB is: Typed  in (new), modified (existing value), delete (value).

In ColumnC there need to be a space and a dash between the content of ColumA and ColumnB

example:
ColumnA = John Denver
ColumnB = Singer
ColumnC = John Denver - Singer

Thank you
Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Ryan McCauley

8/22/2022 - Mon
Ryan McCauley

Your requirement seems pretty straightforward - are you looking for something like this?

CREATE TRIGGER [dbo].[trg_YourTable_iu]
ON [dbo].[YourTable]
AFTER INSERT, UPDATE
AS
BEGIN
   UPDATE YourTable
      SET ColumnC = ColumnA + ' - ' + ColumnB
END

Open in new window


If there are special circumstances, like what you want to do if ColumnA or ColumnB are blank, then you'll have to code for those as well (maybe using a CASE statement)
Cobra967

ASKER
Thank you much, it apper to work fine, however it gives me a lot of empty spaces beween ColumnA and ColumnB in ColumnC.

Example:
John Denver                                  - Singer


Ryan McCauley

Ah - your source data contains those spaces, and that's easily fixed. Change the update query to this:

   UPDATE YourTable
      SET ColumnC = LTRIM(RTRIM(ColumnA)) + ' - ' + LTRIM(RTRIM(ColumnB))

Open in new window

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Cobra967

ASKER
Ok one more problem, the update takes place for all the records in the table not just the record I have updated. In my table I do have a FacilityID as Primary key.
ASKER CERTIFIED SOLUTION
Ryan McCauley

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question