SQL Server to Oracle Language Translator

Question

I have a trigger created in Microsoft SQL Server that I am trying to bring over into Oracle SQL Developer. I realize the syntax and language is a little different. I am slowly but surely trying to work my way through each line, but any help or guidance is greatly appreciated.

The trigger in Microsoft SQL Server that I am trying to carry over to Oracle:

CREATE TRIGGER Production.Product_Price_Check
ON AdventureWorks.Production.Product FOR UPDATE
AS 
    DECLARE @min_price money; --to keep minimum price
    DECLARE @new_price money; --to keep new price from update query

    SELECT @min_price = (SELECT StandardCost*1.2 FROM INSERTED);
    SELECT @new_price = (SELECT ListPrice FROM INSERTED)
    IF @new_price < @min_price
       BEGIN
            ROLLBACK TRANSACTION
            -- Rolls back an explicit or implicit transaction to the beginning of the transaction
            PRINT('the price can’t be below ' + cast(@min_price as varchar(20)));
            -- cast is used to convert one data type to another one 
            RAISERROR ('statement was aborted', 6, 1) ;
            return; 
    ELSE PRINT ('Price was successfully changed');     
    END   

GO

I'll post updates to where I am at with the new trigger as I am researching:

UPDATE Product set ListPrice=42.00 WHERE ProductID=514;

Updated Code:

CREATE OR REPLACE
TRIGGER Product_Price_Check
   BEFORE UPDATE ON Product
   FOR EACH ROW
       BEGIN
         IF :new.listprice < :new.standardcost * 1.2 then
         raise_application_error(-20999, 'The price can not be below' || to_char(:new.standardcost * 1.2));
         RETURN;
         ELSE
           dbms_output.put_line('Price was sucessfully changed');
         END IF;
END;

Example code to quickly create Table I am using:

CREATE TABLE Product(
    productID int Primary Key,
    name VARCHAR(250),
    ListPrice int Primary Key,
    StandardCost NUMBER(10,4),
);

INSERT INTO Product VALUES(514, 'NLL Mountain Seat Assembly', 133.3400, 98.7700);

user_15498 · Answer

In either Oracle or SQL Server, it looks like the trigger is unnecessary and what you should instead write:

ALTER TABLE Production.Product ADD CONSTRAINT CK_Product_Prices
CHECK (ListPrice >=StandardCost * 1.2);

This avoids writing any procedural logic at all and instead is using declarative rules. These are usually preferred because at times, the optimizer can make use of them to eliminate portions of a query which couldn't possibly produce any results.

user_330315 · Answer

The most fundamental difference between triggers in Oracle and SQL Server are, that Oracle uses row level triggers by default - something that is not available with SQL Server. As far as I can tell, all you want to do is to check if the new values of updated row comply with certain rules.

CREATE or replace TRIGGER Product_Price_Check_trg
  BEFORE UPDATE ON Product 
  FOR EACH ROW -- this makes it a row level trigger
AS 
begin
  if :new.listprice < :new.standardcost * 1.2 then 
    raise_application_error(-20999, 'The price can''t be below ' || to_char(:new.listprice));
  end if;
end;
/

Because of the exception the transaction doing this has to be rolled back.

More details on the syntax and examples how to create a trigger can be found in the manual

However there is no need for a trigger, a check constraint will be much more efficient (in Oracle just as well as in SQL Server)

create table product
(
   standardcost number,
   listprice    number, 
   constraint check_price check (listprice < standardcost * 1.2)
);

SQL Server to Oracle Language Translator

2 Answers