Update only new batch records on T-SQL Trigger

Question

I am trying to Parse Address information passed in as a comma separated string. I have gotten as far as parsing down the information as I need using this pseudo sample

CREATE TRIGGER aftInsParseAddress
AFTER INSERT 
AS
    DECLARE @address VARCHAR(100),
        @city VARCHAR(100),
        @stateZip VARCHAR(100),
        @source varchar(100),
        @state varchar(2) ,
        @zip varchar(5), 
        @country1 varchar(10) ,
        @country varchar(3) 

    SET @source = (SELECT AddressSourceA 
                   FROM table 
                   WHERE ID = 'GUID-GUID-GUID-GUID-GUID' AND Exported = 0)

    SELECT  
        @address = LEFT(@source, CHARINDEX(',', @source) - 1),
        @city = (SUBSTRING (@source, CHARINDEX(',', @source) + 2 ,
                                     CHARINDEX(',', @source, CHARINDEX(',', @source) + 1) - LEN(LEFT(@source, CHARINDEX(',', @source) )) -2)),
        @stateZip = SUBSTRING(@source, CHARINDEX(',', @source, LEN(@city) + LEN(@address)), 10),
        @state = (SUBSTRING(@stateZip, 3 ,4)),
        @zip = (SUBSTRING(@stateZip, 6, 9)),
        @country1 = (SUBSTRING(@source, (CHARINDEX(',', @source, LEN(@address) + LEN(@statezip) + LEN(@city) )), 5)),
        @country = (SUBSTRING(@country1, 3, 7))

    UPDATE SameTableInsertedInto 
    SET Address = @address, City = @city, 
        State = @state, Zip = @zip, 
        Country = @country 
    WHERE id IN (SELECT ID FROM inserted);

I would start with a string as my @source = '1600 Pennsylvania Ave NW, Washington, DC 20500, USA'

Ending up with the values parsed out how I need them i.e.

1600 Pennsylvania Ave NW
Washington 
DC 
20500
USA

I'm importing this data from a web form that the creator isn't capable of parsing the address on their end. As a result I need each address field split up in its own variable. I have built a sample that does this and tried to implement this in a trigger on after insert then update the same fields in the destination table.

I am able to build a working sample of successfully triggering 1 record but in more often I would be importing multiple records. I'm not sure where to head next with this to fix my dilemma. Should I be parsing the data within a stored procedure or function ?

Also important to note. I am attempting to run this trigger after I insert records into my table, and update values within the same table the the new values are going into.

Thanks in advance for any help !

The [`INSERTED` table can have multiple rows](https://learn.microsoft.com/en-us/sql/relational-databases/triggers/create-dml-triggers-to-handle-multiple-rows-of-data), so your trigger is going to need to be revised to handle that. I recommend putting the parsing logic into an [inline table valued function.](https://technet.microsoft.com/en-us/library/ms189294(v=sql.105).aspx) Separation of concerns should help. — Steven Ensslen, Nov 02 '17 at 22:28
in addition, doing lengthy string manipulation in a trigger would not be advisable.... — Mitch Wheat, Nov 03 '17 at 01:33
I suggest doing it in batch afterwards with a scheduled proc rather than doing it in a trigger. Any error in the trigger (and using substring on dirty data makes errors pretty much inevitable) will fail the insert of the entire record. You don't want that happening. — Nick.Mc, Nov 03 '17 at 06:08
Thanks for the suggestion. I am trying to address the separation of concerns as I am aware of the benefits. I just am unsure of where to go from here. Ideally this address would be parsed from the text box input on the web form through php , but the developer claims that they cannot do that. I am trying to adjust this for batch processing. I just need to find out what parts to build next to allow processing batch records. — Carlos Lopez, Nov 03 '17 at 18:12
How would you recommend adjusting the insert query to handle multiple rows ? Any pseudo code should help. — Carlos Lopez, Nov 03 '17 at 19:05
I was able to achieve my desired results with the suggested inline table value function and a cursor. Thanks for the help ! — Carlos Lopez, Nov 07 '17 at 16:34

score 0 · Answer 1 · answered Nov 03 '17 at 01:17

0

Parsing addresses is really hard. See good reference here: How to parse freeform street/postal address out of text, and into components

That said, if you find a good algorithm for doing this (instead of using MelissaData service), and if you are determined to run it inside a SQL Trigger, then you best put the code into a CLR stored procedure. In a CLR Stored proc, you will have the advantage of a much better language (C#, VB) for implementing such an algorithm, rather than TSQL. Here is a library that performs the job (YMMV) https://usaddress.codeplex.com

good luck.

answered Nov 03 '17 at 01:17

Mike A

51
2

Thanks for the suggestion. I am primarily trying to write this through a trigger/ stored procedure. The underlying application executing this SQL is a MS-Access application. I am trying to build a closed end loop that can import records from mySQL into SQL. Which is being called by underlying VBA code. Ideally it would be nice to write something in C# or VB but that may require additional setup. If make it to that point I'll try and implement a C# library using LINQ I think i could easily achieve this. However, this is not in my project currently and would only extend the scope of this task. – Carlos Lopez Nov 03 '17 at 18:57

Update only new batch records on T-SQL Trigger

1 Answers1