LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1879
Members Online:
1
Guests Online:
83

Total Post History
Posts:
81451
Topics:
18716

7-Day Post History
New Posts:
0
New Topics:
0
Active Topics:
0

Our newest member
mwojcik

Other

FAQ
All times are UTC [ DST ]

Trigger: Update a column based on change of data in another

Microsoft SQL Server
Please wait...

Trigger: Update a column based on change of data in another

Postby ravidora2004 on Thu Feb 28, 2013 11:18 pm

Hi friends I have table where i save the path of folders and their subfolders of an application is the following way"

Table name:Customers

FolderID Folder ProjectID ParentID Path
1 Regular 20 0 Null
2 Texas 20 1(folder Id) Regular
3 Houston 20 2 Regular/Texas


So basically whenever i want to (update)change the folder name say Regular to Daily i would like to check for all the data in Path field and do the necessary change like shown below


FolderID Folder ProjectID ParentID Path
1 Daily 20 0 Null
2 Texas 20 1(folder Id) Daily
3 Houston 20 2 Daily/Texas


How can i perform this task..Thank you in advance...
ravidora2004
Newbie
Newbie
 
Posts: 2
Joined: Thu Feb 28, 2013 11:17 pm
Unrated

Re: Trigger: Update a column based on change of data in another

Postby SQLDenis on Fri Mar 01, 2013 6:03 pm

you can do it by comparing the deleted and inserted pseudo tables, if Folder changed then update all the other rows

This is designed badly, ideally you only want to store Regular in 1 place then when you update it, the change would be reflected because of the FK pointing back
User avatar
SQLDenis
LTD Admin
LTD Admin
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
LTD Gold - Rating: 3467LTD Gold - Rating: 3467LTD Gold - Rating: 3467
 
Posts: 21784
Joined: Wed Oct 10, 2007 6:43 pm
Location: Princeton, New Jersey, USA,World, Solar System, Milky Way, Universe and Beyond
Unrated