locked
Need a trigger to update same row after insert RRS feed

  • Question

  • Hi Everyone,

    I am looking to create a trigger that will update a row or record right after that record has added to the database. We used to call this an after insert trigger.

    Specifically, after the row has been inserted, I would like to have the uniqueidentifier field (named Contact_GUID) value put in a variable that will get the string equivalent of the variable and re-insert it into the same row (that field is called Str_Contact_GUID).

    The reason I do this is that I can pass the string equivalent to other applications and still query the same row.

    I know how to translate the uniqueidentifier into a string equivalent.(I have the code for that)

    I only need the code to update the same row that would have been just inserted.

    Thanks so much,

    Jon

    Tuesday, February 7, 2012 9:54 PM

Answers

  •  I would like to have the uniqueidentifier field (named Contact_GUID) value put in a variable that will get the string equivalent of the variable and re-insert it into the same row (that field is called Str_Contact_GUID).

    Why do you have to do that? Here is a simple GUID conversion to varchar:

    SELECT GUIDSTRING=CONVERT(varchar(37),newid())
    -- B7173239-2408-44EE-A29D-D5DA59E93D67


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    • Marked as answer by jonsuns7 Wednesday, February 8, 2012 12:40 AM
    Tuesday, February 7, 2012 10:14 PM

All replies

  •  I would like to have the uniqueidentifier field (named Contact_GUID) value put in a variable that will get the string equivalent of the variable and re-insert it into the same row (that field is called Str_Contact_GUID).

    Why do you have to do that? Here is a simple GUID conversion to varchar:

    SELECT GUIDSTRING=CONVERT(varchar(37),newid())
    -- B7173239-2408-44EE-A29D-D5DA59E93D67


    Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES

    • Marked as answer by jonsuns7 Wednesday, February 8, 2012 12:40 AM
    Tuesday, February 7, 2012 10:14 PM
  • Agree with Kalman. 

    You only need to set a default value for the column, 

    see this e.g

    create table t1(id int,ncid varchar(37) default (CONVERT(varchar(37),newid())))
      
    insert into t1(id) values(1),(2),(3)
    
    select * from t1

    vt


    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    Tuesday, February 7, 2012 10:28 PM