SQL Server Trigger for Insert, Rookie

Question

I have a database that is getting populated from data extracted from an Autocad drawing. The data is very limited and there for I need to extract values from the string text I bring in from a column DWG_NUMBER and populate values in other blank columns in the same table from DWG_NUMBER.

I know I need to do this with a trigger during insert. I am having problems figuring out how. I can accomplish what I am trying to do VIA a query, can someone please give me some help?

Here is what I have:

• Database table = ID_TAG
• Columns with the data I need to extract are = DWG_NUMBER, IDNUMBER, DESCRIPTION
• Columns I need to populate are JOB_NUMBER, QR_CODE

Here is the query I wrote that gives me what I am looking for:

My query

Code:

SELECT TOP 1000 
    *, [Handle], [BlockName],
    [IDNUMBER], [DESCRIPTION], [QTY],
    [TAG_TYPE], [TAGGED_SCAN], [CRATE_SCAN], 
    [DATE], [DWG_NUMBER],
    LEFT(RIGHT(DWG_NUMBER, LEN(DWG_NUMBER) -2), LEN(DWG_NUMBER) - 4) AS JOB_NUMBER,
    CONCAT(LEFT(RIGHT(DWG_NUMBER, LEN(DWG_NUMBER) - 2), LEN(DWG_NUMBER) - 4), '|', [IDNUMBER], '|', [DESCRIPTION]) AS QR_CODE
FROM 
    [PCS_Shipping].[dbo].[ID_TAG]

Answer 1

If you want to do this with a trigger, try this code:

CREATE TRIGGER trgIDTagInsert
ON dbo.ID_TAG
AFTER INSERT
AS 
BEGIN
    UPDATE dbo.ID_TAG t1
    SET JOB_NUMBER = LEFT(RIGHT(i.DWG_NUMBER, LEN(i.DWG_NUMBER) -2), LEN(i.DWG_NUMBER) - 4),
        QRCODE = CONCAT(LEFT(RIGHT(i.DWG_NUMBER, LEN(i.DWG_NUMBER) - 2), LEN(i.DWG_NUMBER) - 4), '|', i.IDNUMBER, '|', i.DESCRIPTION)
    FROM Inserted i 
END

Basically, you define a trigger on a table (ID_TAG) and for a given operation (here: INSERT).

Next, you need to understand that the trigger is called once per statement - not once per row - so if you have an INSERT statement that inserts 20 rows at once, your trigger is called only once - but the pseudo table Inserted will contain all those 20 new rows (with the exact same columns as your "base" table).

So you need to grab into Inserted and get the values that were inserted, and then you can update the base table ID_TAG by setting the JOB_NUMBER and QRODE columns, based on columns from the Inserted rows.

This code does the whole updating with a single, set-based operation and should be quite nice and fast. You want to avoid cursors and while loops at all costs in triggers!

And as @LaughingVergil mentioned: you could also define those two columns as computed columns right in your table which would be automagically computed for every new row you insert - without then need for a trigger.

Just add these two statement to your table create script:

ALTER TABLE dbo.ID_Tag
ADD Job_Number AS LEFT(RIGHT(DWG_NUMBER, LEN(DWG_NUMBER) -2), LEN(DWG_NUMBER) - 4) PERSISTED;

ALTER TABLE dbo.ID_Tag
ADD QR_Code AS CONCAT(LEFT(RIGHT(DWG_NUMBER, LEN(DWG_NUMBER) - 2), LEN(DWG_NUMBER) - 4), '|', [IDNUMBER], '|', [DESCRIPTION]) PERSISTED;

and now you have two new computed columns (you cannot insert any values into these yourself - you also need to always define the list of column for an INSERT and omit these two from the list of columns) that will always be up to date and contain the values you need