Trigger Help!

• dba321

  Hall of Fame

  Points: 3917

  More actions

  December 29, 2005 at 9:34 am

  #364500

  The below is definitely not formed correctly just as an FYI so I need all the help I can get at a low-level explanation.

  Create Trigger [dbo].[IT_Restrict_AreaCodes]

  ON [dbo].[dialempty]

  AFTER INSERT

  AS

  If

  (select p.projecttype from project p INNER JOIN dialempty on p.ProjectID = inserted.ProjectID) = 2

  AND

  Left(inserted.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)

  BEGIN

  Update dialempty set phonenum = '1111111111'

  END

  What I need this to do is to watch any individual row inserts that happen on the dialempty table.  Check the area code, if in whatever, then set the phone number to all ones.  So far, I just an not sure on the following:

  1) What kind of trigger I should be using, should it be instead of or is my after ok?

  2) Syntax, something's not right, I get this error in Analyzer: The column prefix 'inserted' does not match with a table name or alias name used in the query.

• k_vasanthkumar

  Old Hand

  Points: 349

  More actions

  December 30, 2005 at 1:08 am

  #612089

  I think if you change the code in the following way it would solve ur problem

   

  Create Trigger [dbo].[IT_Restrict_AreaCodes]

  ON [dbo].[dialempty]

  AFTER INSERT

  AS

  If Exists

  (

   select

    p.projecttype

   from

    project p

   INNER JOIN

    inserted I on

   (

    I.ProjectID = p.ProjectID

   AND

    I.ProjectID = 2

   AND

    Left(I.phonenum,3) IN (203,475,860,959,406,218,320,507,612,651,763,952)

  &nbsp

  )

  BEGIN

   Update dialempty set phonenum = '1111111111'

  END