SQL Reference

RULE

RULE in SQL is an object which is associated with a table or a view, containing a condition that every row must meet. The RULE activates when the table or view is modified. RULEs are specific to SQL Server and are primarily used for backward compatibility. Most database systems, including updated versions of SQL Server, favor the use of CHECK constraints over RULEs.

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.

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.