sql server simple insert trigger issue

Question

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

Filip Popović · Accepted Answer · 2011-11-14 10:01:11Z

3

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.

answered Nov 14, 2011 at 10:01

Filip Popović

2,6553 gold badges19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Filip De Vos Over a year ago

Path variables should be 260 characters long. (longer if you allow the long path form with \\?\ prefix.) see msdn.microsoft.com/en-us/library/…

Filip Popović Over a year ago

@FilipDeVos, I upvoted your comment because it is brilliant suggestion for particular case (Windows platform, file paths). However, OP didn't specify what does path relates to (categories, windows path or something else).

Filip De Vos Over a year ago

I assumed MS Sql Server hence windows. The type should also be NVARCHAR by the way. Once bitten, twice shy.

Collectives™ on Stack Overflow

sql server simple insert trigger issue

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related