sql problem

• vivekaj17

  Newbie

  Points: 7

  More actions

  March 16, 2010 at 9:32 pm

  #139417

  hey everyone,

  can anyone tell me how do i keep a common primary key between multiple tables.

  tried it using a trigger which is not working

  for example,i have 4 tables Flat,Bungalow,Plot,Shop.I have a common attribute "prid between them .SO if Flat has a value of prid as "pr1" ,the other tables should not be able to get the value "pr1".

  tried it using the following trigger

  create trigger allow on Flat for insert,update

  as

  begin

  declare @prid as char(10),@flag as varchar(5)

  set @flag='true'

  set @prid=(select fid from inserted)

  if(exists(select fid from Flat where fid=@prid))

  set @flag='false'

  else

  set @flag='true'

  if(exists(select buid from Bungalow where buid=@prid))

  set @flag='false'

  else

  set @flag='true'

  if(exists(select plid from Plot where plid=@prid))

  set @flag='false'

  else

  set @flag='true'

  if(exists(select sid from Shop where sid=@prid))

  set @flag='false'

  else

  set @flag='true'

  begin transaction

  if(@flag = 'true')

  begin

  print'Record has been inserted'

  commit

  end

  else

  begin

  print'Enter Another Property id'

  rollback

  end

  end

• Bhuvnesh

  SSC Guru

  Points: 59351

  More actions

  March 17, 2010 at 4:12 am

  #1135078

  you can keep prid column with datatype uniqueidentifier.

  for definition http://msdn.microsoft.com/en-us/library/ms190215.aspx

  -------Bhuvnesh----------
  I work only to learn Sql Server...though my company pays me for getting their stuff done;-)