August 9, 2013 at 10:41 am
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;
August 9, 2013 at 12:52 pm
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
August 9, 2013 at 9:17 pm
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
Change is inevitable... Change for the better is not.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply