SQLTeam.com | Weblogs | Forums

SQL: HOW to concatenate two fields using trigger,

coding-standards
trigger
sql2016
sql2014

#1

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


#2
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)


#3

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


#4

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;

#5

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.


#6

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


#7

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.


#8

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


#9

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.


#10

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