Trigget to update a field from the same table

Cobra967
Cobra967 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan McCauleySenior Data Architect

Commented:
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)

Author

Commented:
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 McCauleySenior Data Architect

Commented:
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

Author

Commented:
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.
Senior Data Architect
Commented:
My mistake - I meant to join it to the INSERTED virtual table as well so that it only affects your recently changed rows:


   UPDATE YourTable
      SET ColumnC = LTRIM(RTRIM(ColumnA)) + ' - ' + LTRIM(RTRIM(ColumnB))
     FROM YourTable y
     JOIN INSERTED i
       ON y.PrimaryKey = i.PrimaryKey

Open in new window


Assuming you have a primary key on your table, that will work. If not, then you can filter it using something like

WHERE ColumnC <> LTRIM(RTRIM(ColumnA)) + ' - ' + LTRIM(RTRIM(ColumnB))

Open in new window


Though that will be a little more cumbersome, since it will scan every row.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial