SQLTeam.com | Weblogs | Forums

SQL: HOW to concatenate two fields using trigger,

Hi All,
I have SQL database called “S1”, and a table called “WORKORDER”, inside the table, I have two field on is called “ WORKORDERID” and the second field is called “SOURCEWOID” and would like to concantanate these two fields in one fields called “TEXT10”
For example, suppose I have the records in the first and second column as shown, and I would like to write concatenation results as shown in the field text10 manually using a trigger which depends on the “SOURSEWOID” field,

“ WORKORDERID” SOURCEWOID” TEXT10

       10	                       1	                          10,12
        11	                       2	                            11
        12                 	1	                       10,12 

Anyone may send me the code and the trigger, I don’t have any experience in coding.

Thank you in advance
Best,
Majdoleen

CREATE TRIGGER dbo.My_TR_WORKORDER
	ON dbo.WORKORDER
	AFTER INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
SET XACT_ABORT ON 
SET ARITHABORT ON 

	UPDATE	U
	SET	[TEXT10] = COALESCE(CONVERT(varchar(20), I.WORKORDERID) + ',', '')
			 + COALESCE(CONVERT(varchar(20), I.SOURCEWOID)
	FROM	inserted AS I
		JOIN dbo.WORKORDER AS U
			 ON U.MyPKey1 = I.MyPKey1
			AND U.MyPKey2 = I.MyPKey2
			AND ...

but its a really bad idea. What happens if the ID's and the TEXT10 get out of sync - you won't know which value to trust. You are also storing the data twice. And so on.

Use a COMPUTED COLUMN, or create a VIEW (which you use whenever you want to Report on the data and include the [TEXT10] value)

Thank you Kristan for this effort, I tried to copy the code but seems not working, is this the full trigger, I agree with you that I am storing the data twice but I need to display the third field online.

Please may you send me the full trigger, as I mentioned, I dn't have any idea about coding.

Thanks
Majdoleen

I agree with @Kristen that the idea of a trigger to update this type of information seems fraught with peril. I don't know if you can use a computed column since the computed column could potentially need data from more than one column. If you want to create a view, see the example below. This may not be very efficient, but it would work correctly.

-- sample table
CREATE TABLE dbo.tmp (workorderid INT, sourceworkorderId INT);
INSERT INTO tmp VALUES (10,1),(11,2),(12,1);

SELECT * FROM tmp;
GO

-- create a view
CREATE VIEW dbo.tmpView
AS
SELECT
	t.workorderid,
	t.sourceworkorderId,
	STUFF(c.text10,1,1,'') AS text10
FROM
	tmp t
	CROSS APPLY
    (
		SELECT ','+CAST(workorderid AS VARCHAR(32))
		FROM tmp t2
		WHERE t2.sourceworkorderid = t.sourceworkorderid
		FOR XML PATH('')
	) c(text10);
GO

SELECT * FROM dbo.tmpView;

Yup, that's all you need.

Not much to go on ... what sort of "not working"? Error message? Not creating the value in TEXT10 when you do insert/Update? If so what is your Insert/Update statement.

Using a VIEW would be a much better solution.

Hi Kristen,
I just copy the trigger and paste it to my SQL, and get the error attached.


As I mentioned, I don’t have any experience in coding.

What might be my issue?
Best,
Majdoleen

Sorry, I missed the second parameter off the second COALESCE statement, should have been a blank string to prevent propagation of NULL

You seem hell-bent on using this solution even though we've all told you its a really bad idea.

Thank you Kristen for you effort, really appreciate, but I need this feature in my work, despite the fact that it is bad idea!!

I will be very thankful if you provide me the full trigger to implement this in my database, if you don't mind we can have a small session (screen sharing) to fix my issue.

Thank you in advance.

Best,
Majdoleen

You need to understand the code, how to code, and fix the code, otherwise what's the point? - just getting someone else to do your work for you, you'll learn nothing.

How are you going to write & maintain this code if you have no idea how it works? Your company has given you a job to do and not the skills, they need to provide you with training etc. or hire a consultant

Plus as @JamesK and I have said: using a Trigger for this job is a bad idea, whoever asked you to do that needs questioning as to why they think that's the right solution rather than just blindly implementing it.

Kristen, I do understand the code, but don't have the knowledge to write it! I posted my issue on this public forum, and its your choice to answer it or not. do you think creating a new table and applying a query is the best solution? and how to update this table automatically?

As i mentioned before, it is your choice to answer my questions.

Thanks
Majdoleen