当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server simple insert trigger issue

问题描述:

I'm new to triggers . I have table like this

CREATE TABLE [dbo].[Positions](

[Id] [int] IDENTITY(1,1) NOT NULL,

[ParentId] [int] NOT NULL,

[Path] [varchar](100) NULL,

[Title] [nvarchar](200) NOT NULL,

[Description] [nvarchar](1000) NULL

)

Which I'm trying to write a trigger in which when ever a record inserted , the trigger update the path .

The Path = the path of parent + / + Id of new inserted record .

I have a trigger like this , But it all the time set the 1 in Path column which is not correct .

ALTER trigger [dbo].[ti_updatepath]

on [dbo].[Positions]

after insert

as

begin

declare @NewId int = (select Id from Inserted)

declare @NewParentId int = (select parentId from Inserted)

declare @ParentPath varchar ;

set @ParentPath = (select path from positions where Id = @NewParentId)

declare @Path varchar;

set @path = @ParentPath + '/'+ convert(varchar ,@NewId)

update Positions set Path = @path where Id= @NewId

end

for more info , my table populated like this :

Id ParentId Path

1 0 1/

2 1 1/2

3 2 1/2/3

5 2 1/2/5

6 4 1/2/6

7 2 1/2/7

8 2 1/2/8

9 2 1/2/9

10 2 12/10

13 2 1/2/13

14 2 1/2/14

15 2 1/2/15

16 2 1/2/16

17 8 1/2/8/17

18 8 1/2/8/18

19 8 1/2/8/19

20 17 1/2/8/17/20

网友答案:

When declaring character data types You have to specify their length, otherwise, SQL Server will assume they are of length 1 and as such it will show first character only.

You need to declare @ParentPath and @Path as varchar(100) (change 100 to appropriate) and when converting @NewId to varchar You need to convert it to varchar of specified length also:

declare @ParentPath varchar(100);
set @ParentPath = (select path from positions where Id = @NewParentId)

declare @Path varchar**(100);
set @path = @ParentPath + '/'+ convert(varchar(100), @NewId)
update Positions set Path = @path where Id= @NewId

Please note that this trigger will fail if someone inserts multiple records in single INSERT statement. You should rewrite it to support multiple rows inserts.

分享给朋友:
您可能感兴趣的文章:
随机阅读: