SQL Server触发器（我需要从任何给定节点遍历分层树结构） [英] SQL Server trigger (I need to move through a hierarchical tree structure from any given node)

问题描述

美好的一天

我有一个为特定前端应用程序设计的旧数据库。我正在使用这些数据来进行其他应用程序开发的多个案例，但是事实证明，旧数据库不足以适应将来的需求。不幸的是，由于我仍然需要运行前端应用程序，因此旧数据库必须保留在原位。

我创建了一个类似结构的新数据库，每次将车辆（我们将使用的示例）从前面添加到旧数据库时，都会使用该数据库最终应用程序我已经设置了一个触发器，可以在插入时将指定的数据推送到新数据库中（这一切都正常工作）。

现在解决我的问题。在位置的分层树结构中，为每辆车分配了一个位置键，该键描述了它属于哪个位置。我需要从任何树级别获取此位置，并使用locations表在旧数据库中找到它之下和之上的所有节点，然后将节点的所有位置键添加到新数据库中的vehicle表中，包含7个级别（列）。我只需要获取位置 0,1,2,3,4,5,6,7。

例如，我将有7列，其中任何列都可能是车辆的注册位置。

 （Level0Key，Level1Key，Level2key等。 。，...，...，Level6Key，Level7Key）
  

据我了解，您需要为了帮助我，请查看旧版数据库的车辆表，逻辑级别表和位置表（其中所有位置均带有父键）。

我将附上这些表和我拥有的简单触发器，我无法解释id会对逻辑语句或编码触发器可能有用的帮助有多大帮助（奖金）。在此先感谢您。
我只是想将所有LocKeys导出到变量@ level1Key等。

位置表

逻辑级别表

车辆表

代码：

 在
上设置ANSI_NULLS在
上设置QUOTED_IDENTIFIER在
 
上创建触发器dbo.transferVehicle 
在dbo.Vehicles 
插入
后，将
开始
设为NOCOUNT； 
 
声明@ Level0Key INT，@ Level1Key INT，@ Level2Key INT，@ Level3Key INT，@ Level4Key INT，@ Level5Key INT，@ Level6Key INT，@ Level7Key INT，@ LocKey INT; 
 
 SELECT @LocKey = [LocKey] FROM插入的； 
 
，tbParent为
（
 select *来自Canepro.dbo.locations，其中LocKey = @LocKey 
并入所有
选择位置。* from Canepro。 dbo.locations在locations.LocKey = tbParent.ParentKey 
上加入tbParent，
 
 tbsons作为
（
 select * from Canepro.dbo.locations其中LocKey = @ LocKey 
联合所有
个选定位置。*来自Canepro.dbo.locations在位置上加入tbsons.ParentKey = tbsons.LocKey 
），
 tball为
（
 select *从tbParent作为p 
工会
 select *从tbsons作为s 
 
），
最终作为
（
选择编号= ROW_NUMBER（）OVER（按t.LocKey排序，t.LocKey，t.LocName，t.ParentKey 
从tball作为t 
）
 
-我现在需要将所有行（LocKeys）从final导出到变量
中-如果我使用两个select语句（请参见下文），则会得到一个err或在第二个
上，从数字= 1的最终位置选择@ LocKey1 = LocKey，从最后的数字= 2的位置选择@ LocKey2 =洛基，从数字= 2的最终位置选择$ Lock $。 ] .dbo.Vehicles（VehCode，VehicleNumber，RegistrationNumber，Description，FuelKey，CatKey，活动，预期消耗，IsPetrol，LicenseExpiryDate，FuelTankCapacity，OdometerReading，Level0LocKey，Level1LocKey，Level2LocKey，Level3LocKey，Level4LocKey，Level5LocKey，Level6LocKey，Level5LocKey $ b） 
选择
 VehCode，VehicleNumber，RegistrationNumber，Description，FuelType，CatKey，Active，ExpectedConsumption，IsPetrol，LicenseExpiryDate，FuelTankCapacity，OdometerReading，LocKey，@ Level0Key，@ Level1Key，@ Level2Key，@ Level3Key，@ Level4Key， @ Level5Key，@ Level6Key，@ Level7Key-然后所有其他与锁齿相关的节点，从上到下都是从级别0（树的顶部）到级别6的树
从
插入
 END 
 GO 
  

插入的预期输入：

  Vkey：185 
 Lockey：60000690 
 VehCode：52 
 VehicleNumber：80 / 11A52 
 RegistrationNumber： NUF 37746 
描述：福特6610 4x4（52）
燃料类型：174 
 CatKey：7 
有效：1 
预期消耗量：Null 
 IsPetrol：0 
 LicenseExpiryDate：2011-04-30 00:00:00 
 FuelTankCapacity：150 
 OdomenterReading：小时
  

到新数据库的预期输出：

  Vkey：185 
 Lockey：60000690 
 VehCode：52 
车辆编号：80 / 11A52 
注册编号：NUF 37746 
说明：福特6610 4x4（52）
燃料类型：174 
 CatKey：7 
有效：1 
预期消耗：无
 IsPetrol：0 
 LicenseExpiryDate：2011-04-30 00:00:00 
 FuelTankCapacity：150 
 OdomenterReading：小时
 Level0Key：60000291（Top Tree节点）
 Level1Key：600 02764（第二层树）
 Level2Key：60000841（第三层树）
 Level3Key：60000177（第四层树）
 Level4Key：60000179（第五层树）
 Level5Key：60000181（树的第六层）
 Level6Key：60000205（树的第七层）
 Level7Key：60000690（树的第八层）
（我们可以看到这与Lockey）
  

真的很感谢您的帮助

解决方案

问题1

如果我使用两个选择语句，则

见下文）我在第二个错误

这不起作用，因为您的CTE消失了在第一个声明之后。因此，您需要将数据保存到工作表中。

示例：

 -设置一个表变量以将结果保存到
 DECLARE @WorkTable TABLE（LevelNumber INT，LocKey INT，ParentKey INT）
 
 DECLARE @LocKey INT = 11; 
 
，tbParent为
（
 select * from [Location]其中LocKey = @LocKey 
 union all 
选择[Location]。* from [Location] ]在[Location] .LocKey = tbParent.ParentKey 
上加入tbParent，将
 
 tbsons作为
（
 select * from [Location]其中LocKey = @LocKey 
工会所有
选择[Location]。*，从[Location]加入[Location]上的tbsons。ParentKey= tbsons.LocKey 
），
 tball作为
（
 select *从tbParent作为p 
工会
 select *从tbsons作为s 
 
），
最终作为
（
 select LevelNumber = ROW_NUMBER（）OVER（按t.LocKey排序，t.LocKey，t.ParentKey 
来自tball作为t 
）
 
-将结果保存到表变量中
插入@WorkTable（LevelNumber，LocKey，ParentKey）
从最后的
 
中选择LevelNumber，LocKey，ParentKey-现在我们可以对表变量$ b进行所需的操作$ b select @ LocKey1 = LocKey from final where number = 1 
 select @ LocKey2 = Lockey from final where number = 2 
  

但是我必须再次提醒您除非您确定数据总是以这种方式输出，否则将强制自引用树进入固定级别。

问题2

< blockquote>

SELECT @LocKey = [LocKey] FROM插入的;

已插入可以包含许多行。这只是第一个。如果有任何操作会插入或更新许多行，则触发器将无法正常工作。您需要循环（或联接）插入并对其进行处理。

DDL和插入的示例

下面是表DDL和示例数据的示例。这样我们就可以在本地设置您的数据并使用它。

 创建表[位置]（LocKey INT，ParentKey INT， TreeLevel INT）
 
插入[位置] 
选择LocKey，ParentKey，TreeLevel 
 FROM 
（
值
（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）
）作为T（LocKey，ParentKey，TreeLevel）
  

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

解决方案

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)

这篇关于SQL Server触发器（我需要从任何给定节点遍历分层树结构）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！