Insert Trigger

• am-244616

  SSCarpal Tunnel

  Points: 4936

  More actions

  February 19, 2008 at 6:51 am

  #186625

  Guys,

  I have 15 tables where the combination of CODE, range for EFFECTIVE DATE and END DATE columns should be unique.

  For example

  ID CD EFF DT END DT

  ______________________________________

  1 CO 02/02/08 02/15/08

  2 CO 02/04/09 02/29/08

  3 CO 02/16/08 02/29/08

  In the above example the table should throw an error for 2nd row ID = 2 since the effective and end date overlap with

  1st row ID = 1

  Is there any way to accomplish this using triggers on the table?

  Any suggestions and inputs would help

  Thanks

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  February 19, 2008 at 8:55 pm

  #780055

  If you are validating the CD column with the help of Eff Date and End Date, then in the INSERT trigger a query with "Where" clause can help you out.

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga

• Steve Jones - SSC Editor

  SSC Guru

  Points: 728159

  More actions

  February 19, 2008 at 9:29 pm

  #780070

  I think this is simple if I understand what you are looking for.

  In the trigger, look for the inserted table and check if it's dates and code are inside an existing row.

  You could do something like

  create trigger ...

  if exists ( select top 1 cd

  from TableA a

  inserted b

  where b.eff betwen a.eff and a.end

  )

  rollback

  return

  Follow me on Twitter: http://www.twitter.com/way0utwest
  Forum Etiquette: How to post data/code on a forum to get the best help
  My Blog: www.voiceofthedba.com