Query Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Query Help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-13 : 16:08:20

[code]
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
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-13 : 16:15:24

sorry question not clear. Is WAS column name or value of column? without posting DDL it would be hard to suggest solution. so please post DDL of table with sample data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-13 : 16:30:54

are you trying to do some sort of audit trail? i would suggest a different approach if you are trying to do some sort of audit.

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-13 : 17:26:45

[code]

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.
[/code]

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-13 : 17:48:55

like this?



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

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-13 : 18:48:04

Thanks yosiasz

How can i acheive this with trigger...
I have run the manually updates each time when insert done correct !

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-13 : 19:05:01

Something like this?


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

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2012-06-13 : 20:21:35

Thanks It worked..

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2012-06-13 : 20:43:48

cool only worry I have is with the following
2.16.840.1.114222 what is the largest length value in each "octect".

<><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources