SQL/MX 3.2.1 Reference Manual (H06.26+, J06.15+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2.1 Reference Manual—691117-005
2-152
Examples of CREATE TRIGGER
CURRENT_DATE, 'POUND OFF', '200 mg', '1 pill 1 hour before
each meal', 0, 'Free sample no refills'
);
•
This example
Rowsets and Triggers
Suppose that you have a table with this rowset definition:
Rowset[10] short ArrayA;
This embedded DML statement inserts ten rows into table tab1.
EXEC SQL insert into cat.sch.tab1 values (:ArrayA);
If trigger trg1 is defined as an insert statement trigger on tab1,and trg2 is defined
as an insert row trigger on tab1, when the DML statement is executed, the two
triggers are fired. The action of trg1 executes once for the entire statement, while
trg2 executes ten times, once for each element in the rowset.
Stored Procedures and Triggers
Starting with SQL/MX Release 3.2, the trigger statements support stored procedures.
The considerations are:
•
IN type of procedure parameters are supported.
•
SPJ with resultset is not supported.
•
AFTER triggers are supported.
•
You must ensure that there is no recursion between SPJ and trigger tables as it
can cause undefined behavior.
The following example creates a trigger that executes an stored procedure, named
LOWERPRICE defined on page 2-92
, when the QTY_ON_HAND column of the
PARTLOC table is updated and exceeds 500 parts. For definition of tables, see
Appendix D, Sample Database.
CREATE TRIGGER sales.setsalesprice
AFTER UPDATE OF qty_on_hand
ON invent.partloc
FOR EACH STATEMENT
REFERENCING NEW as newqty
WHEN ( SUM(newqty.qty_on_hand) > 500 )
CALL sales.lowerprice();