Populating required column with a trigger

  • Sorry if this has been covered before. I have lots of Oracle experience, but I am a newby to SQL Server.

    I have a table that is being populated from a stored procedure. The table has a required field that the stored procedure may not have any data for and the column is part of a unique key. What I want to do is to use the primary key of the row being inserted. Is there a way to get the identity before I insert and use that value for both the primary key and my column?

    In Oracle I could select the value from the sequence and then use that for both columns if necessary. Is there something similar in SQL Server? Or can I use a trigger and get the value of the id and use that if my colulmn is null? i.e.

    IF :NEW.myColumn IS NULL THEN

    :NEW.myColumn = :NEW.myPrimaryKey;

    END IF;

    Thanks for any ideas!

  • Use sql to get the next id

    eg as part of your update statement

    insert into table 1 ( (PK column 1) , column 2, etc

    Values (

    (Select TOP 1 (PK_COLUMN) from Table 2 ORDER BY PK_COLUMN DESC) + 1 As PK_TO_INSERT,

    Column 2 Value

    )

  • If you wish to enter an identity you can check it through @@identity which being a global variable will give you the next identity value

    Also you can set a default value for that particular column ... Only if that suits your design and put value in the variable only if you have data coming from the stored procedure

    Hope this helps ...

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

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