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.
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.
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;
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.
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.
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.
