t-sql

• sathishsynl

  Newbie

  Points: 1

  More actions

  April 25, 2008 at 3:24 am

  #184904

  Hello,

  i sathish getting problem in the triggers for this coding

  insered trigger query.

  create trigger trig1

  on emp

  for insert

  as

  begin

  if((select count(emp_id)

  from emp,inserted

  where inserted.emp_id = emp.emp_id)>1)

  begin

  print'duplicate are not allowed'

  rollback

  end

  else

  commit

  end

  error message is this

  Msg 209, Level 16, State 1, Procedure trig1, Line 6

  Ambiguous column name 'emp_id'.

  regards,

  sathish.

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 25, 2008 at 3:27 am

  #807024

  In your SELECT COUNT(emp_ID) you need to declare which table that you are using to return the emp_ID from: Emp or Inserted.

  😉

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  April 25, 2008 at 3:56 am

  #807035

  Why don't you just put a unique constraint on the emp_id column? No need for a trigger at all.

  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

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  April 25, 2008 at 4:00 am

  #807036

  Gail,

  That'll teach me to RTFS!!

  Well spotted! :w00t:

  Ade.

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]