Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16

    Unanswered: Converting a Oracle PL/SQL command into MS SQL

    hi

    I was wondering if anyone would be able to advise on converting oracle PL/SQL features into MSSQL.

    For example i have the following sequence + trigger below but cant find any information on creating a sequence in MSSQL, is it possible?

    create sequence a_SEQ
    start with 001
    increment by 1

    create or replace trigger a_TG
    before insert a
    for each row
    begin
    select (concat('D',(cast(a_SEQ.nextval as varchar(4))))) into :new.a_id from dual;
    end;


    any links or tutorials would be great, i've got a couple of MSSQL 2005 books which do explain triggers but examples are really needed to understand the full functionality.

    cheers

  2. #2
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280
    As it was for SQL 2000, the SQL 2005 Books Online (BOL) is for me the quickest way to research something.

    The Oracle squence is one of the things I miss in SQL Server. The thing that comes close is an IDENTITY column. In your example you seem to generate an unique number for a table. So define a_id as an IDENTITY column (see CREATE TABLE in the BOL) and forget about the trigger.

    Note, after rereading this, the IDENTITY column works better/simpler than a sequence

  3. #3
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    hi there

    yes that identity column works great and only takes a second through the GUI.

    Just with oracle we were taught to insert a letter before the unique ID to help identify the tables more, (this was done using a sequence + trigger).

    for example on a table called detective instead of:
    ID fname sname
    1 bil fish
    2 fred frog
    3 dave dog

    It would display:
    ID fname sname
    D1 bil fish
    D2 fred frog
    D3 dave dog

    This would help identify that the ID was coming from the detective table.

    Do you know a way of doing something along these lines with MS SQL.

    cheers

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would say don't do it.

    You know what table it's in and what column it's in

    Personally I would avoid surrogate keys
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jan 2007
    Location
    West Midlands
    Posts
    16
    OK

    cheers for the advise

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Brett Kaiser
    Personally I would avoid surrogate keys
    Sputter...choke...cough...
    ...but anyway, PROPER use of surrogate keys would not require adding prefixes to indicate their location. That should be discouraged. A surrogate key should have no inherent relationship to the data it identifies.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •