SQL Server trigger (I need to move through a hierarchical tree structure from any given node)

Question

Good day

I have a legacy database that was designed for a specific front end application. I am doing multiples cases of additional app development using this data however, the legacy database has proven inadequate to work with going into the future. Unfortunately the legacy database has to stay in place due to the fact that i still need the front end application running.

I have created a new database of similar structure that will be used, every time a vehicle (the example we'll use) is added to the legacy database through the front end application I have set up a trigger to push the specified data into the new database on insert (this is all working perfectly).

Now to get to my problem. Each vehicle is allocated a location key which describes which location it belongs to on the hierarchical tree structure of locations. I need to take this location which could be from any tree level and find all the nodes below and above it in the legacy database using the locations table, then add all the location keys of the nodes to the vehicle table in the new database which will comprise of 7 levels (columns). I only need to get Location 0,1,2,3,4,5,6,7.

For example I will have seven columns of which any may be the vehicles registered location.

(Level0Key, Level1Key, Level2key,...,...,..., Level6Key, Level7Key)

As I understand you'll need to see the legacy databases vehicles table, logical level table and locations table (where all locations are listed with there parent keys) in order to help me.

I will attach these tables and the simple trigger I have, I cannot explain how much id appreciate any help whether its a statement of logic or the coded trigger that might work (Bonus). A huge thanks in advance. I am just battling with exporting all the LocKeys to the variables @level1Key etc..

Locations Table

Logical levels table

Vehicles table

Code:

         SET ANSI_NULLS ON
    SET QUOTED_IDENTIFIER ON
    GO

    CREATE TRIGGER dbo.transferVehicle
    ON dbo.Vehicles
    AFTER INSERT 
    AS
    BEGIN
        SET NOCOUNT ON;

        DECLARE @Level0Key INT, @Level1Key INT, @Level2Key INT, @Level3Key INT, @Level4Key INT, @Level5Key INT,@Level6Key INT,@Level7Key INT, @LocKey INT;

        SELECT @LocKey = [LocKey] FROM Inserted ;

            with tbParent as
    (
        select * from Canepro.dbo.locations where LocKey= @LocKey
        union all
        select locations.* from Canepro.dbo.locations  join tbParent  on locations.LocKey = tbParent.ParentKey
    ),

        tbsons as
    (
        select * from Canepro.dbo.locations where LocKey= @LocKey
        union all
        select locations.* from Canepro.dbo.locations  join tbsons  on locations.ParentKey= tbsons.LocKey
    ),
        tball as
    (
        select * from  tbParent as p
        union 
        select * from tbsons as s

    ),
    final as
    (
    select number = ROW_NUMBER() OVER (ORDER BY t.LocKey), t.LocKey,t.LocName ,  t.ParentKey 
    from tball as t 
    )

    --I now need to export all rows (LocKeys) from final into the variables
    -- if i use two select statments (see below) i get an error on the second
select @LocKey1 = LocKey from final where number = 1
select @LocKey2 = Lockey from final where number = 2 


        INSERT INTO [NewDatabase].dbo.Vehicles (VehCode, VehicleNumber, RegistrationNumber, Description, FuelKey, CatKey, Active, ExpectedConsumption, IsPetrol, LicenseExpiryDate, FuelTankCapacity, OdometerReading, Level0LocKey, Level1LocKey, Level2LocKey,Level3LocKey, Level4LocKey, Level5LocKey, Level6LocKey, Level7Key)

            SELECT 
                VehCode, VehicleNumber, RegistrationNumber, Description, FuelType, CatKey, Active, ExpectedConsumption, IsPetrol, LicenseExpiryDate, FuelTankCapacity, OdometerReading, LocKey, @Level0Key, @Level1Key, @Level2Key, @Level3Key, @Level4Key, @Level5Key, @Level6Key, @Level7Key -- then all the other nodes that relate to the lockey, above and below is level from level0 (The top of the tree) to level 6 of the tree
            FROM   
                inserted;
    END
    GO

Expected input from insert:

Vkey : 185 
Lockey : 60000690
VehCode : 52
VehicleNumber : 80/11A52
RegistrationNumber :NUF 37746
Description : Ford 6610 4x4 (52)
FuelType : 174
CatKey : 7
Active : 1
Expected consumption : Null
IsPetrol : 0
LicenseExpiryDate : 2011-04-30 00:00:00
FuelTankCapacity : 150
OdomenterReading : Hours 

Expected output into new database :

Vkey : 185 
Lockey : 60000690
VehCode : 52
VehicleNumber : 80/11A52
RegistrationNumber :NUF 37746
Description : Ford 6610 4x4 (52)
FuelType : 174
CatKey : 7
Active : 1
Expected consumption : Null
IsPetrol : 0
LicenseExpiryDate : 2011-04-30 00:00:00
FuelTankCapacity : 150
OdomenterReading : Hours 
Level0Key : 60000291 (Top Tree node)
Level1Key : 60002764 (Second Level of tree)
Level2Key : 60000841 (third level of tree)
Level3Key : 60000177 (Fourth level of tree)
Level4Key : 60000179 (Fifth level of tree)
Level5Key : 60000181 (sixth level of tree)
Level6Key : 60000205 (seventh level of tree)
Level7Key : 60000690 (Eighth level of tree)  
( We can see this one is the same as the Lockey)

Would really really appreciate some help

Answer 1

Problem 1

if i use two select statments (see below) i get an error on the second

This doesn't work because your CTE's disappear after the first statement. So you need to save the data into a work table.

Example:

-- Set up a table variable to save results into
DECLARE @WorkTable TABLE (LevelNumber INT,LocKey INT,ParentKey INT)

DECLARE @LocKey INT = 11;

        with tbParent as
(
    select * from [Location] where LocKey= @LocKey
    union all
    select [Location].* from [Location]  join tbParent  on [Location].LocKey = tbParent.ParentKey
),

    tbsons as
(
    select * from [Location] where LocKey= @LocKey
    union all
    select [Location].* from [Location]  join tbsons  on [Location].ParentKey= tbsons.LocKey
),
    tball as
(
    select * from  tbParent as p
    union 
    select * from tbsons as s

),
final as
(
select LevelNumber = ROW_NUMBER() OVER (ORDER BY t.LocKey), t.LocKey,  t.ParentKey 
from tball as t 
)

-- Save the results into the table variable
INSERT INTO @WorkTable (LevelNumber,LocKey,ParentKey)
SELECT LevelNumber,LocKey,ParentKey from final

-- now we can do what we like with the table variables
select @LocKey1 = LocKey from final where number = 1
select @LocKey2 = Lockey from final where number = 2 

But again I must caution you against forcing a self referencing tree into fixed levels unless you are certain the data always comes out this way.

Problem 2

SELECT @LocKey = [LocKey] FROM Inserted ;

INSERTED can contain many rows. This just gets the first one. If there is any operation that inserts or updates many rows, your trigger won't work properly. You need to loop (or join) inserted and work on every row in it.

Example of DDL and Inserts

Below is an example of table DDL and sample data. This allows us to set up your data and work with it locally.

CREATE TABLE [LOCATION] (LocKey INT , ParentKey INT , TreeLevel INT)

INSERT INTO [LOCATION]
SELECT LocKey,ParentKey,TreeLevel
FROM 
(
VALUES 
(1,60000291,1),
(2,50000199,6),
(6,60000706,8),
(7,60000707,8),
(8,6,9),
(9,6,9),
(10,6,9),
(11,6,9),
(12,6,9),
(13,6,9),
(14,6,9),
(15,6,9),
(16,6,9),
(17,6,9)
) As T(LocKey,ParentKey,TreeLevel)