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);

Answer 1

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.