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.

cheersAs 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 :)|||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|||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|||OK

cheers for the advise|||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.