CODEWITHSUNDEEP
sqlsql-servertriggers
Dindar
Dindar

Reputation: 3235

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

Upvotes: 0

Views: 534

Answers (1)

Filip Popović
Filip Popović

Reputation: 2655

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.

Upvotes: 3

Related Questions