I wanted a trigger when a record new created.

In the below query results..
after the field value "WAS" inserted into the table then i want to update the "isnow" value in the table..

Please advise and help...

declare @sqlfresh table(id int identity(1,1), value varchar(max))
INSERT INTO @sqlfresh
values('2.16.840.1.114222')
INSERT INTO @sqlfresh
values('2.16.840.1.114222.65.61')

 

;with cteJunk(id, policy#, yougi, value)			
AS
(
SELECT sq.id, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#,
       CASE 
          WHEN LEN(Tags.val.value('.', 'VARCHAR(MAX)')) > 4 THen  Tags.val.value('.', 'VARCHAR(MAX)')
          ELSE REPLACE(POWER(10, 5 - LEN(Tags.val.value('.', 'VARCHAR(MAX)'))  ), '1','') + Tags.val.value('.', 'VARCHAR(MAX)')
       END as yougi, value
  FROM(  
		  SELECT id, CAST('<t>' + REPLACE(value, '.', '</t><t>') + '</t>' AS XML) AS TAG  
			FROM @sqlfresh
	  ) TAB 
 CROSS APPLY TAG.nodes('/t') as Tags(val)
 inner join @sqlfresh sq
 on sq.id = TAB.id 
 ) 
 
 
SELECT
   t1.id,
   value was ,
   IsNow = substring((SELECT ( '.' + yougi )
                           FROM cteJunk t2
                           WHERE t1.id = t2.id
                           ORDER BY id
                           FOR XML PATH( '' )
                          ), 2, 1000 
                         )FROM cteJunk t1
GROUP BY id, value

Thanks for faster response..

I am not doing audit trail.. and student table structure

Id int identity(1,1), 
studentId varchar,
name varchar,
stnewid varchar


I will insert the values into the table with below query. 

insert into dbo.student
(
studentId,
name,
stnewid
) 
values
(
'2.16.840.1.114222',
'Ross',
Null
)

Insert into dbo.student
(
studentId,
name,
stnewid
) 
values
('2.16.840.1.114222.65.61',
'Karl',
Null
)

My expected output would be..



studentId                   name         stnewid 
----------------------    ----------    -----------------------------------------
2.16.840.1.114222          Ross        00002.00016.00840.00001.114222
2.16.840.1.114222.65.61    Karl        00002.00016.00840.00001.114222.00065.00061

wanted Null to be padded with zero's when inserted.

INSERT INTO dbo.student(studentId, name,stnewid)
values('2.16.840.1.114222', 'Ross', null)
INSERT INTO dbo.student(studentId, name,stnewid)
values('2.16.840.1.114222.65.61','Karl', null)

 select * from dbo.student

;with cteJunk(id, policy#, yougi, value)			
AS
(
SELECT sq.id, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#,
       CASE 
          WHEN LEN(Tags.val.value('.', 'VARCHAR(MAX)')) > 4 THen  Tags.val.value('.', 'VARCHAR(MAX)')
          ELSE REPLACE(POWER(10, 5 - LEN(Tags.val.value('.', 'VARCHAR(MAX)'))  ), '1','') + Tags.val.value('.', 'VARCHAR(MAX)')
       END as yougi, studentId
  FROM(  
		  SELECT id, CAST('<t>' + REPLACE(studentId, '.', '</t><t>') + '</t>' AS XML) AS TAG  
			FROM dbo.student
	  ) TAB 
 CROSS APPLY TAG.nodes('/t') as Tags(val)
 inner join dbo.student sq
 on sq.id = TAB.id 
 ) 
 
update tgt
   SET tgt.stnewid = substring((SELECT ( '.' + yougi )
                           FROM cteJunk t2
                           WHERE tgt.id = t2.id
                           ORDER BY id
                           FOR XML PATH( '' )
                          ), 2, 1000 
                         )
  from dbo.student tgt
  --inner join cteJunk src
  --on tgt.id = src.id
  
select * From dbo.student

CREATE TRIGGER [dbo].[Student_it] ON [dbo].[Student]
    FOR INSERT
    AS
    BEGIN
		;with cteUpdateStudent(id, policy#, yougi, value)			
		AS
		(
		SELECT sq.id, Tags.val.value('.', 'VARCHAR(MAX)') AS policy#,
			   CASE 
				  WHEN LEN(Tags.val.value('.', 'VARCHAR(MAX)')) > 4 THen  Tags.val.value('.', 'VARCHAR(MAX)')
				  ELSE REPLACE(POWER(10, 5 - LEN(Tags.val.value('.', 'VARCHAR(MAX)'))  ), '1','') + Tags.val.value('.', 'VARCHAR(MAX)')
			   END as yougi, studentId
		  FROM(  
				  SELECT id, CAST('<t>' + REPLACE(studentId, '.', '</t><t>') + '</t>' AS XML) AS TAG  
					FROM Inserted I
			  ) TAB 
		 CROSS APPLY TAG.nodes('/t') as Tags(val)
		 inner join dbo.student sq
		 on sq.id = TAB.id 
		 ) 
		 
		update tgt
		   SET tgt.stnewid = substring((SELECT ( '.' + yougi )
								   FROM cteUpdateStudent t2
								   WHERE tgt.id = t2.id
								   ORDER BY id
								   FOR XML PATH( '' )
								  ), 2, 1000 
								 )
		  from dbo.student tgt
    END