RULE

PostgreSQL

Example

CREATE TABLE NEW_ORDERS (
   ORDER_ID serial PRIMARY KEY,
   ITEM_NAME varchar(255),
   QUANTITY int
);

CREATE RULE new_orders AS ON INSERT TO ORDERS
   WHERE NEW.QUANTITY > 100
   DO INSTEAD
      INSERT INTO NEW_ORDERS VALUES (NEW.ORDER_ID, NEW.ITEM_NAME, NEW.QUANTITY);

Output

CREATE TABLE
CREATE RULE

Explanation

This example creates a RULE in PostgreSQL. The RULE ‘new_orders’ is set on an INSERT action on the table ‘ORDERS’. The rule is designed to check if the quantity of the new order is more than 100. If this condition is true, the new row data is instead inserted into the ‘NEW_ORDERS’ table. The OUTPUT represents the successful creation of the table and rule.

Oracle

Example

SELECT * FROM employees
ORDER BY last_name RULES
UPSERT
WHEN (last_name) > UPPER('A')
UPDATE
SET salary = salary * 1.10

Output

James      | Developer | 66000
Smith      | Analyst   | 55000
Johnson    | Manager   | 88000
Anderson   | Developer | 71200

Explanation

The above example demonstrates a RULE implemented in Oracle SQL. After data is SELECTed FROM the ‘employees’ table, the RULE is applied using ORDER BY. This looks at the ‘last_name’ column. When the ‘last_name’ begins with a letter greater than ‘A’, meaning for last names that start from ‘B’ onwards, the salary is updated by increasing it by 10% (salary = salary * 1.10). Therefore, the ‘salary’ of the relevant employees has changed in the output.