Update rows

Question

Update rows

kapil_kk

SSC-Insane

Points: 21316
More actions
March 11, 2014 at 5:01 am

#284322

I have a scenario:
I have a table tablAA with column IDA which is get referened in some other table like tablB and tablC as a foreign key.
If I update the deleteflag of tablA to 0 then deleteflag of tables tablB and tablC for that row should also set to 0.
what are the ways to achieve this?
tablA
IDA DeleteFlag
1 1
2 1
tablB
IDB IDA DeleteFlag
1 2 1
tablC
IDC IDA DeleteFlag
1 2 1
Thanks 🙂
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You need a trigger for that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 2

GilaMonster (3/11/2014)
You need a trigger for that.

ok...

do we have any alternative other than trigger....

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Sure.

You ensure that every single piece of code that could ever possibly update the deleteflag of tablA also goes and updates the deleteflag of the other tables.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

GilaMonster (3/11/2014)
Sure.
You ensure that every single piece of code that could ever possibly update the deleteflag of tablA also goes and updates the deleteflag of the other tables.

what if I dont know in how many tables tablA column get referenced...first I need to find the referencing tables of tablA then need to update the deleteflag of all those tables...

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

???

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

mcx5000 Old Hand Points: 365 More actions · Answer 6

If you are asking how to determine the tables that reference tablA you can use this:

select FK.table_name as [Source Table]

,FKU.column_name as [Column]

,FK.constraint_name as [Constraint]

,UK.constraint_name

,UK.table_name as [Referenced Table]

,UKU.column_name as [Column]

from Information_Schema.Table_Constraints as FK

inner join Information_Schema.Key_Column_Usage as FKU on FKU.constraint_name = FK.constraint_name

and FK.constraint_type = 'FOREIGN KEY'

inner join Information_Schema.Referential_Constraints as RC on RC.constraint_name = FK.constraint_name

inner join Information_Schema.Table_Constraints as UK on UK.constraint_name = RC.unique_constraint_name

inner join Information_Schema.Key_Column_Usage as UKU on UKU.constraint_name = UK.constraint_name

and UKU.ordinal_position = FKU.ordinal_position

where uk.table_name = 'tablA'

Once you determine the tables/columns that need to be updated you can create a trigger on tablA or you can create a scheduled job to perform the update and keep the tables in sync.