How do I rewrite the below Oracle trigger so it will work on SQL Server? I am migrating from Oracle to SQL Server using SSMA and I get thrown an unparsed SQL error on the below code. Can anyone help me convert this to T-SQL? Thanks in advance.

CREATE OR REPLACE TRIGGER "ISTRAINING"."INSERT_COURSE_DELEGATES" BEFORE 
INSERT ON "COURSE_DELEGATES" FOR EACH ROW declare
 row_locked exception;
 pragma exception_init (row_locked, -54);
begin
 begin
  select next 
  into :new.COURSE_DELE_ID
  from ISTRAINING.sequence
  where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID'
  for update of next nowait;
  exception
  when row_locked then
      raise_application_error (-20002,'Database temporarily locked'); 
 end;
 update ISTRAINING.sequence
 set next=next+1
 where tname='COURSE_DELEGATES' and tcolname='COURSE_DELE_ID';  
end;
The bad news is that SQL Server doesn't support FOR EACH ROW triggers, so you'd have to take extra care to convert the trigger code.

The good news is that this appears to just be doing what can be achieved with an IDENTITY column in SQL Server, and you won't need the trigger at all.

In your CREATE TABLE COURSE_DELEGATES... statement, declare the COURSE_DELE_ID column something like this (use a more appropriate data type if it isn't int):

CREATE TABLE COURSE_DELEGATES (
    COURSE_DELE_ID int NOT NULL IDENTITY(1,1),
    ....
)


Then any rows inserted in COURSE_DELEGATES will get an auto-generated value for COURSE_DELE_ID, starting with 1, and incrementing by 1 for each new row.