Trigger or Constraint??

• Noman Tariq

  SSCommitted

  Points: 1750

  More actions

  May 20, 2009 at 11:02 pm

  #95491

  Hi,

  I need to know the best practices while using Triggers and Constraints in SQL 2005.

  Problem: I have a table 'Employee' and i have to stop user to enter such employee again who have 'Type' is '1' already in our table.

  like if a record with type = 1 is already exists in employee table, it will stop the user to do so.

  Now i have two options to achieve this. one is 'Trigger' and other is 'Constraint'.

  please guide which is the best in a big DBs.

  Thanks,

  Noman

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  May 21, 2009 at 12:12 am

  #998090

  Constraints if possible, triggers only when the constraint can't be implemented. What you have there sounds like a standard unique constraint. If it's not, post more details.

  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

• Noman Tariq

  SSCommitted

  Points: 1750

  More actions

  May 21, 2009 at 12:30 am

  #998095

  Thanks for prompt response..

  Its not like Unique Constraint...

  i want there is only one row with the Type = 3 and can not insert again with type = 3, but with type = 1 or type = 2 could be multiple in that table.

  Please guide.

  Thanks,

  Noman