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

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;

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;

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

1

The real question is: why didn't you use a real sequence in Oracle to begin with?
â€“Â a_horse_with_no_name
47 mins ago

I didn't write the code, I've just inherited it and been tasked with the migration.
â€“Â Sonal
27 mins ago

add a commentÂ |Â

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;

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

1

The real question is: why didn't you use a real sequence in Oracle to begin with?
â€“Â a_horse_with_no_name
47 mins ago

I didn't write the code, I've just inherited it and been tasked with the migration.
â€“Â Sonal
27 mins ago

add a commentÂ |Â

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;

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

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;

sql-server t-sql trigger

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

edited 2 hours ago

a_horse_with_no_name

36.3k769108

edited 2 hours ago

a_horse_with_no_name

36.3k769108

edited 2 hours ago

a_horse_with_no_name

36.3k769108

asked 3 hours ago

Sonal

111

New contributor

asked 3 hours ago

Sonal

111

asked 3 hours ago

Sonal

111

New contributor

Sonal is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

1

The real question is: why didn't you use a real sequence in Oracle to begin with?
â€“Â a_horse_with_no_name
47 mins ago

I didn't write the code, I've just inherited it and been tasked with the migration.
â€“Â Sonal
27 mins ago

add a commentÂ |Â

1

The real question is: why didn't you use a real sequence in Oracle to begin with?
â€“Â a_horse_with_no_name
47 mins ago

I didn't write the code, I've just inherited it and been tasked with the migration.
â€“Â Sonal
27 mins ago

The real question is: why didn't you use a real sequence in Oracle to begin with?
â€“Â a_horse_with_no_name
47 mins ago

I didn't write the code, I've just inherited it and been tasked with the migration.
â€“Â Sonal
27 mins ago

add a commentÂ |Â

1 Answer
1

active

oldest

votes

up vote
2
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.

answered 13 mins ago

db2

7,79312347

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

add a commentÂ |Â

Your Answer

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "182"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
convertImagesToLinks: false,
noModals: false,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);

);

Sonal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218407%2fhow-do-i-rewrite-the-below-oracle-trigger-so-it-will-work-on-sql-server%23new-answer', 'question_page');

);

Post as a guest

Name

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

up vote
2
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.

answered 13 mins ago

db2

7,79312347

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

add a commentÂ |Â

up vote
2
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.

answered 13 mins ago

db2

7,79312347

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

add a commentÂ |Â

up vote
2
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.

answered 13 mins ago

db2

7,79312347

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.

answered 13 mins ago

db2

7,79312347

answered 13 mins ago

db2

7,79312347

answered 13 mins ago

db2

7,79312347

answered 13 mins ago

db2

7,79312347

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

add a commentÂ |Â

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

Or use a Sequence object in SQL 2012+ docs.microsoft.com/en-us/sql/t-sql/statements/â€¦
â€“Â Jonathan Fite
7 mins ago

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
6 mins ago

add a commentÂ |Â

Sonal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sonal is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Post as a guest

Name

Search This Blog

Iyfjky