Informix Dynamic Server V10 . . . Extended Functionality for Modern Business

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;