Post reply

Row level trigger

  • March 24, 2004 at 11:30 pm

    #107490

    Hi all

     I am doing a Db porting project, the code in plsql has to be converted tsql.I have a trigger written for  oracle i need to convert it to tsql.

    CREATE OR REPLACE TRIGGER T_BI_R_TARGET_OBJECTIVE

    BEFORE INSERT

    ON TARGET_OBJECTIVE

    REFERENCING OLD AS OLD NEW AS NEW

    FOR EACH ROW WHEN (NEW.TARGET_OBJECTIVE_ID IN (NULL,0))

    DECLARE

    seq_id NUMBER;

    BEGIN

    SELECT TARGET_OBJECTIVE_ID_SEQ.NEXTVAL INTO seq_id FROM dual;

    :new.TARGET_OBJECTIVE_ID := seq_id;

    END;

    Any suggestion/comments?. Thanks in advance

    Regards

    Sreenath

     

  • March 25, 2004 at 2:05 am

    #500379

    Hi,

    Theres a big section in the SQL server resouce kit about migrating from oracle to sql server.

    I believe the whole thing is online at microsoft.com

    Hope this helps.

  • March 25, 2004 at 5:17 am

    #500399

    What is the outcome of this TRIGGER, looks like either you are getting the sequence id or setting it my Oracle Kung FU ain't the best and I don't have time right now to break out the bible (Oracle Bible that is).

  • March 25, 2004 at 9:27 am

    #500455

    there's no such thing as a "sequence" object in SQLS, so you'll have to take a different approach.  I would suggest altering the application that inserts to this table so that you always send a null id and create the target_object table in sqls with an IDENTITY(1,1) attribute for the [id] column.  If this is too much work, here's the trigger code.

    CREATE TRIGGER tgr_target_object_id

     ON target_object

     FOR INSERT

    AS

    IF inserted.[id] IN (null, 0)

     DECLARE @max_id INT

     SELECT @max_id = MAX([id]) FROM target_object

     UPDATE target_object

     SET [id] = @max_id + 1

    FROM inserted i

    Or something quite like it.  Check BOL for syntax.  Not too sure about that UPDATE... FROM statement. 

    This was my first post.  How'd I do?


    "Sono daverro allergico alle anguille sotto aceto."

  • March 26, 2004 at 11:14 am

    #500712

    Oracle uses SEQUENCE to generate new, unique ID numbers for rows.

    SQL Server most commonly uses the IDENTITY feature to accomplish this. Look in SQL Books Online for details. You can declare one integer-type column in any table as an IDENTITY column, and it will automatically be populated with a value when a row is inserted.


    R David Francis

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

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