Converting an Oracle trigger to SQL Server 2008

  • Hello -

    I'm new to SQL Server and this forum. I'm trying to convert a simple Oracle trigger to SQL Server 2008. I know the BEFORE needs to be converted to INSTEAD OF but have no idea of the nomenclature. Thanks in advance, any assistance is appreciated.

    CREATE OR REPLACE TRIGGER SET_COMBINELIKECATEGORIES

    BEFORE INSERT ON CATEGORY

    FOR EACH ROW

    BEGIN

    :new.combinelikecategories := 0;

    END;

  • First of all, you (very sadly) don't get to use CREATE OR REPLACE in SQL Server. You need to drop the object you're creating, then CREATE it. You can say FOR INSERT, FOR UPDATE or FOR DELETE to denote which DML operation the trigger applies to.

    The Oracle :NEW pseudo-table is the INSERTED table in SQL Server. The concept is identical, but you don't need the colon before it; it's just available by name. It contains the incoming records being changed by the INSERT or UPDATE. Similarly, the other pseudo-table is DELETED. It contains the records being deleted by the DELETE. Don't ever name one of your tables INSERTED or DELETED. Yes, you can actually do this, but don't.

    A good thin is that I've never suffered from the Oracle mutating exception in SQL Server, but then again that just might be because I try to avoid triggers because of the overhead and I don't even try to use the table being impacted in the first place when I do have to write them.

    Of everything that's different, the thing I miss the most is not having CREATE OR REPLACE. It's the simple things in life that make us happy. 🙂

    HTH

  • rodgersem (8/9/2013)


    Hello -

    I'm new to SQL Server and this forum. I'm trying to convert a simple Oracle trigger to SQL Server 2008. I know the BEFORE needs to be converted to INSTEAD OF but have no idea of the nomenclature. Thanks in advance, any assistance is appreciated.

    CREATE OR REPLACE TRIGGER SET_COMBINELIKECATEGORIES

    BEFORE INSERT ON CATEGORY

    FOR EACH ROW

    BEGIN

    :new.combinelikecategories := 0;

    END;

    Wouldn't it be a heck of a lot easier to just put a DEFAULT of "0" o the CombineLikeCategories column of the Category table instead of using a trigger?

    --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 3 posts - 1 through 2 (of 2 total)

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