Call DBMS_ALERT.SIGNAL in a trigger : DBMS_ALERT « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_ALERT

SQL>
SQL> CREATE TABLE s_inventory
  2  (product_id               NUMBER(7),
  3   warehouse_id             NUMBER(7),
  4   amount_in_stock          NUMBER(9),
  5   reorder_point            NUMBER(9),
  6   max_in_stock             NUMBER(9),
  7   out_of_stock_explanation VARCHAR2(255),
  8   restock_date             DATE);

Table created.

SQL>
SQL> INSERT INTO s_inventory VALUES (10011, 101, 650, 625, 1100, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (10012, 101, 600, 560, 1000, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (10013, 101, 400, 400, 700, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (10021, 101, 500, 425, 740, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (10022, 101, 300, 200, 350, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (10023, 101, 400, 300, 525, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (20106, 101, 993, 625, 1000, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (20108, 101, 700, 700, 1225, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (20201, 101, 802, 800, 1400, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (20510, 101, 1389, 850, 1400, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (20512, 101, 850, 850, 1450, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (30321, 101, 2000, 1500, 2500, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (30326, 101, 2100, 2000, 3500, NULL, NULL);

1 row created.

SQL> INSERT INTO s_inventory VALUES (30421, 101, 1822, 1800, 3150, NULL, NULL);

1 row created.

SQL>
SQL> CREATE OR REPLACE TRIGGER au_inventory
  2     AFTER UPDATE of amount_in_stock
  3     ON s_inventory
  4     FOR EACH ROW
  5     WHEN (new.amount_in_stock < new.reorder_point)
  6  BEGIN
  7     DBMS_ALERT.SIGNAL('REORDER_THRESHOLD_ALERT', 'Warehouse: ' ||
  8        :new.warehouse_id || '  Product: ' || :new.product_id ||
  9        '  Current Stock: ' || :new.amount_in_stock ||
 10        '  Reorder Point: ' || :new.reorder_point);
 11  END au_inventory;
 12  /


SQL>
SQL> show error
SQL>
SQL> drop table s_inventory;

Table dropped.

SQL>
SQL>