Post reply

New to triggers, how to reformat a record that was just inserted

  • February 21, 2006 at 5:15 pm

    #168465

    I am trying to use a trigger to fix phone numbers being entered with a -

    422-434-5555

     

    here is my trigger but it update the whole table, I just want the trigger to look at ONLY the records just inserted or updated and strip the dashes if they are present.

     

    now I have this,

    --Create Trigger

    Create Trigger Trg_Remove_Phone_Dashes on MyBusiness.dbo.Customers

    For Insert, Update

    As

    Update Customers 

    Set Phone =  Replace(Phone,'-','')   

     

     

  • February 21, 2006 at 5:41 pm

    #622479

    Books On Line has a good section on triggers.  You may want to use an "Instead of" trigger to do your processing on the data before it is inserted into your table.  That will give you your control over modifying only the data that you are about to insert/update.

    elliott

  • February 21, 2006 at 5:47 pm

    #622481

    cool, so all i have to do is instead of trigger and it runs the replace function only on records being inserted/updated before they are inserted??!!

  • February 21, 2006 at 5:48 pm

    #622482

    Exact-a-mente

  • February 21, 2006 at 7:12 pm

    #622492

    "Instead of" triggers require you to do the actual insert/update.  That's not a problem but does require some extra effort.

    The "after" trigger you tried to make will work with a very small modification...

    Create Trigger Trg_Remove_Phone_Dashes on MyBusiness.dbo.Customers

    For Insert, Update

    As

    Update Customers 

    Set Phone =  Replace(Phone,'-','')

    FROM Customers c

    INNER JOIN Inserted i

    ON c.pk = i.pk

    "PK", in this case, would be replaced by the name of the column that provides the primary key for the Customers table... usually a customer number or some other surrogate key.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

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