Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Converting an Oracle trigger to SQL Server 2008 Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 10:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 9, 2013 2:59 PM
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;
Post #1482859
Posted Friday, August 9, 2013 12:52 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Thursday, August 28, 2014 1:00 PM
Points: 4,196, Visits: 3,235
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
Post #1482900
Posted Friday, August 9, 2013 9:17 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:58 AM
Points: 36,995, Visits: 31,517
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1482999
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse