March 27, 2017 at 8:48 am
I've got an (AFTER INSERT) trigger that I want to:
1) parse the (multi-row) INSERTED table, looking for a specific columns data(a string value)
2) If a specific string is found, write out string data to another column in the table the trigger is acting on
I thought I had it working with a CURSOR, but was advised against it in favor of SET based...but I struggled to make that work
properly.
I've since been advised to utilise the OUTPUT function, with a temp table...
I'm just not sure the best way to handle "looping" through INSERTED table etc...?
Thanks much,
Rich
March 27, 2017 at 8:51 am
I guess u can try while loop instead of cursor.
March 27, 2017 at 9:10 am
rgouette - Monday, March 27, 2017 8:48 AMI've got an (AFTER INSERT) trigger that I want to:
1) parse the (multi-row) INSERTED table, looking for a specific columns data(a string value)
2) If a specific string is found, write out string data to another column in the table the trigger is acting onI thought I had it working with a CURSOR, but was advised against it in favor of SET based...but I struggled to make that work
properly.
I've since been advised to utilise the OUTPUT function, with a temp table...I'm just not sure the best way to handle "looping" through INSERTED table etc...?
Thanks much,
Rich
Can you share your original code with DDL, sample data and expected results? The column names and data don't need to be real, just something we can test on.
March 27, 2017 at 9:13 am
You have not really provided enough detail.
Assuming the column to be updated is in the same row, try the following outline:
UPDATE Y
SET YourUpdateColumn = 'WhatYouWantToUpdate'
FROM YourTable Y
WHERE EXISTS
(
SELECT *
FROM inserted I
WHERE Y.PK = I.PK
)
AND Y.YourSearchColumn = 'YourSearch';
March 27, 2017 at 9:19 am
caojunhe24 - Monday, March 27, 2017 8:51 AMI guess u can try while loop instead of cursor.
No, a WHILE loop suffers from the same performance issues as a cursor does. Indeed, if anything, it may perform even worse.
John
March 27, 2017 at 9:22 am
sorry for the lack of detail, here's what I tried: BEGIN
UPDATE o
SET generic_custom_description = i.order_no +' '+ RTrim(LTrim(SUBSTRING(i.extended_desc, CharIndex('Customer Bin:',i.extended_desc)+13,255)))
FROM oe_line o
INNER JOIN INSERTED i
ON o.line_no = i.line_no
WHERE o.extended_desc like '%Customer Bin:%'
END
March 27, 2017 at 9:35 am
rgouette - Monday, March 27, 2017 9:22 AMsorry for the lack of detail, here's what I tried:BEGIN
UPDATE o
SET generic_custom_description = i.order_no +' '+ RTrim(LTrim(SUBSTRING(i.extended_desc, CharIndex('Customer Bin:',i.extended_desc)+13,255)))
FROM oe_line o
INNER JOIN INSERTED i
ON o.line_no = i.line_no
WHERE o.extended_desc like '%Customer Bin:%'
END
Post what I asked for, that piece of code doesn't help. Read the articles on my signature to know how to post what we need to help.
March 27, 2017 at 10:50 am
I doubt the PK of oe_line is line_no. It it likely to be something like order_no, line_no.
Your JOIN should probably be:
FROM oe_line o
INNER JOIN inserted i
ON o.order_no = i.order_no
AND o.line_no = i.line_no
March 27, 2017 at 10:52 am
rgouette - Monday, March 27, 2017 9:22 AMsorry for the lack of detail, here's what I tried:BEGIN
UPDATE o
SET generic_custom_description = i.order_no +' '+ RTrim(LTrim(SUBSTRING(i.extended_desc, CharIndex('Customer Bin:',i.extended_desc)+13,255)))
FROM oe_line o
INNER JOIN INSERTED i
ON o.line_no = i.line_no
WHERE o.extended_desc like '%Customer Bin:%'
END
Can't you use a computed column? That's effectively what you are attempting to emulate in code.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
March 27, 2017 at 10:54 am
Table with sample Data:
DROP TABLE #OE_LINE_RICH
CREATE TABLE #OE_LINE_RICH(
order_no VARCHAR(8) NOT NULL PRIMARY KEY
,line_no DECIMAL(19, 0) NOT NULL
,extended_desc VARCHAR(255) NULL
,oe_hdr_uid INTEGER NOT NULL
,generic_custom_description VARCHAR(255) NULL
);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000001,1,'MyExtendedDesc Customer Bin: Some Bin Info',2,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000622,11,'MyExtendedDesc',3,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000012,2,'MyExtendedDesc Customer Bin: Some Bin Info',3,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000002,3,'MyExtendedDesc Customer Bin: Some Bin Info',3,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000003,12,'MyExtendedDesc',4,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000004,13,'MyExtendedDesc',5,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000005,14,'MyExtendedDesc',6,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000006,15,'MyExtendedDesc',7,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000007,16,'MyExtendedDesc Customer Bin: Some more Bin Info',8,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000008,17,'MyExtendedDesc',9,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000009,18,'MyExtendedDesc',10,NULL);
INSERT INTO #OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description) VALUES (1000019,29,'MyExtendedDesc Customer Bin: Even More Bin Info',10,NULL);
Expected results:
+==========+=========+============================================+============+============================+
| order_no | line_no | extended_desc | oe_hdr_uid | generic_custom_description |
+==========+=========+============================================+============+============================+
| 1000001 | 1 | MyExtendedDesc Customer Bin: Some Bin Info | 2 | Some Bin Info|
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000002 | 3 | MyExtendedDesc Customer Bin: Some Bin Info | 3 | Some Bin Info|
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000003 | 12 | MyExtendedDesc | 4 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000004 | 13 | MyExtendedDesc | 5 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000005 | 14 | MyExtendedDesc | 6 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000006 | 15 | MyExtendedDesc | 7 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000007 | 16 | MyExtendedDesc Customer Bin: Some Bin Info | 8 | Some Bin Info|
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000008 | 17 | MyExtendedDesc | 9 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000009 | 18 | MyExtendedDesc | 10 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000012 | 2 | MyExtendedDesc Customer Bin: Some Bin Info | 3 | Some Bin Info|
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000019 | 29 | MyExtendedDesc Customer Bin: Some Bin Info | 10 | Some Bin Info |
+----------+---------+--------------------------------------------+------------+----------------------------+
| 1000622 | 11 | MyExtendedDesc | 3 | NULL |
+----------+---------+--------------------------------------------+------------+----------------------------+
What I've tried:
BEGIN
UPDATE o
SET generic_custom_description = i.order_no +' '+ RTrim(LTrim(SUBSTRING(i.extended_desc, CharIndex('Customer Bin:',i.extended_desc)+13,255)))
FROM oe_line o
INNER JOIN INSERTED i
ON o.line_no = i.line_no
WHERE o.extended_desc like '%Customer Bin:%'
END
March 27, 2017 at 10:57 am
rgouette - Monday, March 27, 2017 9:22 AMFROM oe_line o
INNER JOIN INSERTED i
ON o.line_no = i.line_no
I doubt the PK of oe_line is line_no. It it likely to be something like order_no, line_no.
Your JOIN should probably be:
FROM oe_line o
INNER JOIN inserted i
ON o.order_no = i.order_no
AND o.line_no = i.line_no
You might be onto something, since I'm seeing 2 PK's
......
order_no
and
line_no
I'll look into that..
thx,
Rich
March 27, 2017 at 12:07 pm
The update seems to be working correctly with the sample data. The results don't match but that's only because you're adding the order number to the description.
CREATE TABLE OE_LINE_RICH(
order_no VARCHAR(8) NOT NULL PRIMARY KEY
,line_no DECIMAL(19, 0) NOT NULL
,extended_desc VARCHAR(255) NULL
,oe_hdr_uid INTEGER NOT NULL
,generic_custom_description VARCHAR(255) NULL
);
GO
CREATE TRIGGER TI_OE_LINE_RICH ON OE_LINE_RICH
AFTER INSERT
AS
UPDATE o
SET generic_custom_description = i.order_no +' '+ RTrim(LTrim(SUBSTRING(i.extended_desc, CharIndex('Customer Bin:',i.extended_desc)+13,255)))
FROM OE_LINE_RICH o
INNER JOIN INSERTED i
ON o.order_no = i.order_no
AND o.line_no = i.line_no
WHERE o.extended_desc like '%Customer Bin:%'
GO
INSERT INTO OE_LINE_RICH(order_no,line_no,extended_desc,oe_hdr_uid,generic_custom_description)
VALUES
(1000001,1,'MyExtendedDesc Customer Bin: Some Bin Info',2,NULL)
,(1000002,3,'MyExtendedDesc Customer Bin: Some Bin Info',3,NULL)
,(1000003,12,'MyExtendedDesc',4,NULL)
,(1000004,13,'MyExtendedDesc',5,NULL)
,(1000005,14,'MyExtendedDesc',6,NULL)
,(1000006,15,'MyExtendedDesc',7,NULL)
,(1000007,16,'MyExtendedDesc Customer Bin: Some more Bin Info',8,NULL)
,(1000008,17,'MyExtendedDesc',9,NULL)
,(1000009,18,'MyExtendedDesc',10,NULL)
,(1000012,2,'MyExtendedDesc Customer Bin: Some Bin Info',3,NULL)
,(1000019,29,'MyExtendedDesc Customer Bin: Even More Bin Info',10,NULL)
,(1000622,11,'MyExtendedDesc',3,NULL);
GO
SELECT *
FROM OE_LINE_RICH
GO
DROP TABLE OE_LINE_RICH;
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply