Triggers, Sequences and Indexes

Triggers Fire on Events

CREATE OR REPLACE TRIGGER trg_customer_audit
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    INSERT INTO audit_log(what) VALUES ('Inserted ' || :NEW.name);
  ELSIF DELETING THEN
    INSERT INTO audit_log(what) VALUES ('Deleted ' || :OLD.name);
  END IF;
END;
/

:NEW and :OLD expose the new and old row values inside the trigger.

Sequences Generate Numbers

CREATE SEQUENCE order_seq START WITH 1000 INCREMENT BY 1;
SELECT order_seq.NEXTVAL FROM dual;
SELECT order_seq.CURRVAL FROM dual;

Indexes

CREATE INDEX idx_customers_email ON customers(email);
CREATE UNIQUE INDEX idx_customers_email_uniq ON customers(email);
CREATE BITMAP INDEX idx_orders_status ON orders(status);  -- low-cardinality column

Key Points

• Triggers run automatically before or after DML.
• :NEW and :OLD give row context to triggers.
• Sequences supply unique numbers independently of tables.
• Bitmap indexes suit columns with few distinct values.