SQL/MX 3.1 Query Guide (H06.23+, J06.12+)

SQL/MX Operators
HP NonStop SQL/MX Release 3.1 Query Guide—663851-001
7-87
VALUES Operator
The following is an example of the VALUES operator:
create table table_a
(col1 char(5) NOT NULL NOT DROPPABLE
, col2 int NOT NULL NOT DROPPABLE
, col3 smallint
, CONSTRAINT table_a_PRIMARY_KEY PRIMARY KEY (col1 ASC, col2
ASC) NOT DROPPABLE
);
create table table_b
(col1 char(5) NOT NULL NOT DROPPABLE
, col2 int NOT NULL NOT DROPPABLE
, col3 smallint
, owner_count int
, CONSTRAINT table_b_PRIMARY_KEY PRIMARY KEY (col1 ASC, col2
ASC) NOT DROPPABLE
);
ALTER TABLE table_a
ADD CONSTRAINT table_a_KEY FOREIGN KEY
(col1, col2) REFERENCES
table_b(col1, col2) DROPPABLE ;
CREATE TRIGGER table_a_Owner_Count
AFTER INSERT ON table_a
REFERENCING NEW AS newrow
FOR EACH ROW
UPDATE table_b SET owner_count = (
SELECT count(*) FROM table_a
WHERE (table_b.col1,table_b.col2)=
(table_a.col1,table_a.col2)
AND table_a.col3 = 1
)
WHERE (table_b.col1,table_b.col2)=
(newrow.col1,newrow.col2);
insert into table_b values('A', 1, 1, 0);
Prepare TestQuery11 from
insert into table_a values('A', 1, 1);
DESCRIPTION
fragment_id ............ 0
parent_frag ............ (none)
fragment_type .......... master
parent_frag The fragment_id for the parent of the current
fragment. The value is (none) for the master
executor.
integer
fragment_type Master, ESP, or DP2. text
tuple_expr The tuple produced by this node. expr(text)
TP663851.fm Page 87 Monday, October 17, 2011 11:48 AM