Mega Code Archive
Call DBMS_ALERT SIGNAL in a trigger
<![CDATA[
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>
]]>