SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Converting an Oracle trigger to SQL Server 2008


Converting an Oracle trigger to SQL Server 2008

Author
Message
rodgersem
rodgersem
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
Hello -
I'm new to SQL Server and this forum. I'm trying to convert a simple Oracle trigger to SQL Server 2008. I know the BEFORE needs to be converted to INSTEAD OF but have no idea of the nomenclature. Thanks in advance, any assistance is appreciated.

CREATE OR REPLACE TRIGGER SET_COMBINELIKECATEGORIES
BEFORE INSERT ON CATEGORY
FOR EACH ROW
BEGIN
:new.combinelikecategories := 0;
END;
Ed Wagner
Ed Wagner
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45289 Visits: 10835
First of all, you (very sadly) don't get to use CREATE OR REPLACE in SQL Server. You need to drop the object you're creating, then CREATE it. You can say FOR INSERT, FOR UPDATE or FOR DELETE to denote which DML operation the trigger applies to.

The Oracle :NEW pseudo-table is the INSERTED table in SQL Server. The concept is identical, but you don't need the colon before it; it's just available by name. It contains the incoming records being changed by the INSERT or UPDATE. Similarly, the other pseudo-table is DELETED. It contains the records being deleted by the DELETE. Don't ever name one of your tables INSERTED or DELETED. Yes, you can actually do this, but don't.

A good thin is that I've never suffered from the Oracle mutating exception in SQL Server, but then again that just might be because I try to avoid triggers because of the overhead and I don't even try to use the table being impacted in the first place when I do have to write them.

Of everything that's different, the thing I miss the most is not having CREATE OR REPLACE. It's the simple things in life that make us happy. :-)

HTH


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)SSC Guru (204K reputation)

Group: General Forum Members
Points: 204959 Visits: 41952
rodgersem (8/9/2013)
Hello -
I'm new to SQL Server and this forum. I'm trying to convert a simple Oracle trigger to SQL Server 2008. I know the BEFORE needs to be converted to INSTEAD OF but have no idea of the nomenclature. Thanks in advance, any assistance is appreciated.

CREATE OR REPLACE TRIGGER SET_COMBINELIKECATEGORIES
BEFORE INSERT ON CATEGORY
FOR EACH ROW
BEGIN
:new.combinelikecategories := 0;
END;


Wouldn't it be a heck of a lot easier to just put a DEFAULT of "0" o the CombineLikeCategories column of the Category table instead of using a trigger?

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search