sql server - convert oracle trigger to ms sql trigger -

i have oracle trigger:

create or replace trigger  "bi_info"  before insert on "info" each row  when (new."id" null or new."id" = 0) begin     select nvl(max("id")+1, 1) :new."id" "info"; end; 

now want convert ms sql trigger.

i know [id] [int] identity(1,1) can implement trigger similarly

but not want id auto add one.

thanks.

1. ms sql doesn't support before triggers. should rather use instead of triggers.
2. ms sql triggers set-based rather row-based triggers, should taken account

that being said can try

create trigger tg_bi_info_insert on bi_info instead of insert begin   declare @max int    set @max = (select coalesce(max(id), 0) bi_info (tablockx, holdlock))    insert bi_info (id, column1, ...)   select @max + row_number() on (order (select 1)), column1, ...     inserted end 

here sqlfiddle demo

if want emulate part when (new."id" null or new."id" = 0) allowing insert explicitly assigned ids then

create trigger tg_bi_info_insert on bi_info instead of insert begin   declare @max int    insert bi_info   select *      inserted    coalesce(id, 0) <> 0    set @max = (select coalesce(max(id), 0) bi_info (tablockx, holdlock))    insert bi_info (id, column1)   select @max + row_number() on (order (select 1)), column1     inserted    coalesce(id, 0) = 0 end 

here sqlfiddle demo case