How do I rewrite the below Oracle trigger so it will work on SQL Server?

Question

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;

Answer 1

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.

Answer 2

Gap Free Sequential Number

The Oracle code implements a "gap free sequential number" Business Requirement for the ID column.

It appears that the table used to maintain "current ID" is used for multiple tables.

Get rid of this Business Requirement ASAP!!!!

"Gap free sequential numbers" should only be used for Human consumption after you have upper management sign (in triplicate) that they are OK with the known performance trade-off.

Primary reason I've seen for such Business Requirement is for Auditors. Sadly, this comes from a paper world where "paper trails" are made of ... paper. In the digital age, there are other ways to see if someone has modified data or a missed "Invoice"/"PO" number was (never) used.

One "valid usage" would be for document Revision/Version numbers.

Even then, when "gap free sequential numbers" are used, you should use a surrogate key as the PK.

Recommendation

1. Get approval to remove the "gap free sequential number" requirement.
2. Drop the trigger and use a SEQUENCE/IDENTITY column.