Chapter 3. The SQL language 123
action statements can be any DML query or an EXECUTE remote procedure
statement that refers to an across database built-in UDT. Non built-in UDTs must
cast to built-in UDTs or built-in types for this functionality to work.
Example 3-48 shows the statements to insert a TRIGGER statement.
Example 3-48 Simple insert TRIGGER
CREATE TRIGGER new_order_audit
INSERT ON ordertab REFERENCING NEW AS post
FOR EACH ROW
(EXECUTE PROCEDURE inventorydb:mkperisable
(new. item_num, new.order_shelf_life))
Example 3-49 shows the statements to update a TRIGGER.
Example 3-49 Simple update TRIGGER
CREATE TRIGGER change_order_audit
UPDATE OF order_desc on ordertab
REFERENCING OLD AS pre NEW AS post
FOR EACH ROW WHEN ( pre.order_shelf_life = "t" )
(UPDATE inventorydb:itemtab
SET item_life = pre.order_shelf_life)
3.6.7 Distinct type query support
All distinct types of built-in UDTs and built-in data types are supported for cross
database select queries. Distinct types can be cross database references of
columns in column list, predicates, sub queries, UDR parameters and return
types. The distinct types, their hierarchies, and their cast should all be the same
across all the cross databases in the query. Example 3-50 shows a select for
orders weighing more that the item weight for the same item number.
Example 3-50 Select using distinct types
DATABASE inventorydb;
CREATE DISTINCT TYPE pound AS FLOAT;
CREATE DISTINCT TYPE kilos AS FLOAT;
CREATE FUNCTION ret_kilo(p pound) RETURNS kilos;
DEFINE x float;
124 Informix Dynamic Server V10 . . . Extended Functionality for Modern Business
LET x = p::float / 2.2;
RETURN x::kilos;
END FUNCTION;
CREATE FUNCTION ret_pound(k kilos) RETURNS pound;
DEFINE x float;
LET x = k::float * 2.2;
RETURN x::pound;
END FUNCTION;
CREATE implicit cast (pound as kilos with ret_kilo);
CREATE explicit cast (kilos as pound with ret_pound);
ALTER TABLE itemtab ADD (weight pound);
DATABASE orderdb;
CREATE DISTINCT TYPE pound AS float;
CREATE DISTINCT TYPE kilos AS float;
CREATE FUNCTION ret_kilo(p pound) RETURNS kilos;
DEFINE x float;
LET x = p::float / 2.2;
RETURN x::kilos;
END FUNCTION;
CREATE FUNCTION ret_pound(k kilos) RETURNS pound;
DEFINE x float;
LET x = k::float * 2.2;
RETURN x::pound;
END FUNCTION;
CREATE IMPLICIT CAST (pound AS kilos WITH ret_kilo);
CREATE EXPLICIT CAST (kilos AS pound WITH ret_pound);
ALTER TABLE ordertab ADD (weight kilos);
SELECT x.order_num, x.weight
FROM orderdb:ordertab x
inventorydb:itemtab y
WHERE x.item_num = y.item_num AND
x.weight > y.weight -- compares pounds to kilos.
GROUP BY x.weight;

Get Informix Dynamic Server V10 . . . Extended Functionality for Modern Business now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.