Link to home
Start Free TrialLog in
Avatar of Cobra967
Cobra967Flag 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
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

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)
Avatar of 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


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

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
Avatar of Ryan McCauley
Ryan McCauley
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial