up vote 1 down vote favorite

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;
share|improve this question
  • 1
    The real question is: why didn't you use a real sequence in Oracle to begin with? – a_horse_with_no_name Sep 24 at 13:19
  • I didn't write the code, I've just inherited it and been tasked with the migration. – Sonal Sep 24 at 13:39
up vote 3 down vote

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.

share|improve this answer
  • 2
    Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/… – Jonathan Fite Sep 24 at 13:58
  • 1
    A Sequence is better as it allows you to generate the values first and then insert them, which is more compatible with the model you have in Oracle. – David Browne - Microsoft Sep 24 at 14:00
  • Thanks for this, I've used this solution and it's fixed after days of going over it. Appreciate it! – Sonal Sep 24 at 15:36
  • 1
    @DavidBrowne-Microsoft Yes, though in this case, it appears that the Oracle trigger isn't doing anything with the values prior to inserting them, and doesn't appear to have multiple parallel sequences in the same table, so for this situation, a simple IDENTITY column will reduce maintenance overhead. If the application ever needs to preview the new values before the insert happens, or needs to generate multiple sequences within the same table based on different columns (a bit like MySQL can do with MyISAM), then you'll definitely need a sequence object. – db2 Sep 24 at 16:21
  • Oh, though I believe there are some performance improvements you can get from a sequence (correct me if I'm wrong here), so if this is a heavily loaded OLTP application with tons of inserts, then that may make it worth the effort to set up a sequence object. – db2 Sep 24 at 16:25
up vote 0 down vote

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.
share|improve this answer

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.