Trigger Help!

  • 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.

  • 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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply