Triggers

• An SQL Trigger is a mechanism that automatically executes a specified set of SQL statements when a triggering event occurs on a table.
• Basically, a trigger consists of a triggered event and a triggered action.
• The triggering event may be one of INSERT, DELETE, UPDATE
• The triggered action may be one of INSERT, DELETE, UPDATE, EXECUTE PROCEDURE

General Syntax of SQL CREATE TRIGGER statement:

CREATE TRIGGER trigger_name
{INSERT ON table_name | 
 DELETE ON table_name | 
 UPDATE OF column_name ON table_name}
[REFERENCING NEW AS name OLD AS name]
{FOR EACH ROW | BEFORE | AFTER} (Action List);

(1) Enforcing Referential Integrity Constraint.
    Upon deleting a customer record, automatically
    delete all orders for that customer from the
    orders table.

CREATE TRIGGER del_cust
DELETE ON customers
REFERENCING OLD AS pre_del
FOR EACH ROW (DELETE FROM orders WHERE cid = pre_del.cid);

(2) Creating an Audit Trail.
    Each time someone updates the quantity of a particular
    product, make an entry in a log file of this update
    along with the name of the person doing the update and
    the date of update.
CREATE TABLE log (
  pid           varchar(3),
  username      char(8),
  update_date   date,
  old_qty       integer,
  new_qty       integer);

CREATE TRIGGER upd_prod
UPDATE OF quantity ON products
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW (INSERT INTO log 
              values (pre_upd.pid,USER,CURRENT,
                      pre_upd.quantity,post_upd.quantity));

(3) Implementing Business Rules:
    Rule: NO single update SHALL increase the total
          quantity of all products in stock by 50% or more.

CREATE PROCEDURE upd_prod_1()
  DEFINE GLOBAL old_qty INT DEFAULT 0;
  LET old_qty = (SELECT SUM(quantity) FROM products);
END PROCEDURE;

CREATE PROCEDURE upd_prod_2()
  DEFINE GLOBAL old_qty INT DEFAULT 0;
  DEFINE new_qty INT;
  LET new_qty = (SELECT SUM(quantity) FROM products);
  IF new_qty > 1.5 * old_qty THEN
      RAISE EXCEPTION -746, 0, "Update Not Allowed";
  ENDIF
END PROCEDURE

CREATE TRIGGER upd_prod
UPDATE OF quantity ON products
BEFORE(EXECUTE PROCEDURE upd_prod_1())
AFTER(EXECUTE PROCEDURE upd_prod_2());

Note: If a trigger fails, INFORMIX automatically rollbacks all
      changes.