SQL Server触发器（我需要从任何给定节点遍历分层树结构）

本文介绍了SQL Server触发器（我需要从任何给定节点遍历分层树结构）的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 美好的一天 我有一个为特定前端应用程序设计的旧数据库。我正在使用这些数据来进行其他应用程序开发的多个案例，但是事实证明，旧数据库不足以适应将来的需求。不幸的是，由于我仍然需要运行前端应用程序，因此旧数据库必须保留在原位。 我创建了一个类似结构的新数据库，每次将车辆（我们将使用的示例）从前面添加到旧数据库时，都会使用该数据库最终应用程序我已经设置了一个触发器，可以在插入时将指定的数据推送到新数据库中（这一切都正常工作）。 现在解决我的问题。在位置的分层树结构中，为每辆车分配了一个位置键，该键描述了它属于哪个位置。我需要从任何树级别获取此位置，并使用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 已插入可以包含许多行。这只是第一个。如果有任何操作会插入或更新许多行，则触发器将无法正常工作。您需要循环（或联接）插入并对其进行处理。 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 dayI 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 tableCode: 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 secondselect @LocKey1 = LocKey from final where number = 1select @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 GOExpected input from insert:Vkey : 185 Lockey : 60000690VehCode : 52VehicleNumber : 80/11A52RegistrationNumber :NUF 37746Description : Ford 6610 4x4 (52)FuelType : 174CatKey : 7Active : 1Expected consumption : NullIsPetrol : 0LicenseExpiryDate : 2011-04-30 00:00:00FuelTankCapacity : 150OdomenterReading : Hours Expected output into new database : Vkey : 185 Lockey : 60000690VehCode : 52VehicleNumber : 80/11A52RegistrationNumber :NUF 37746Description : Ford 6610 4x4 (52)FuelType : 174CatKey : 7Active : 1Expected consumption : NullIsPetrol : 0LicenseExpiryDate : 2011-04-30 00:00:00FuelTankCapacity : 150OdomenterReading : 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 1This 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 intoDECLARE @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 variableINSERT INTO @WorkTable (LevelNumber,LocKey,ParentKey)SELECT LevelNumber,LocKey,ParentKey from final-- now we can do what we like with the table variablesselect @LocKey1 = LocKey from final where number = 1select @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 2INSERTED 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 InsertsBelow 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,TreeLevelFROM (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触发器（我需要从任何给定节点遍历分层树结构）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！