--#COMMENT ----------------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- FILE: SQL_VRN.HTML ---
--#COMMENT --- AUTHOR: G.BIRCHALL ---
--#COMMENT --- DATE: 04/SEP/2003 ---
--#COMMENT --- ---
--#COMMENT --- NOTES: This file contains some very rough ---
--#COMMENT --- sample DDL that illustrates how one ---
--#COMMENT --- can use a combination of DB2 triggers ---
--#COMMENT --- and views to do the following: ---
--#COMMENT --- ---
--#COMMENT --- - Record every change to the data ---
--#COMMENT --- in an application (auditing). ---
--#COMMENT --- - Show the state of the data, as ---
--#COMMENT --- it was, at any point in the past ---
--#COMMENT --- (historical analysis). ---
--#COMMENT --- - Follow the sequence of changes to ---
--#COMMENT --- any item (e.g. customer), or set ---
--#COMMENT --- of items, in the database. ---
--#COMMENT --- - Do "what if" analysis by creating ---
--#COMMENT --- virtual copies of the real world ---
--#COMMENT --- and then changing them as desired, ---
--#COMMENT --- without affecting the real-world ---
--#COMMENT --- data. ---
--#COMMENT --- ---
--#COMMENT --- The schema uses tables, which hold ---
--#COMMENT --- all states of the data, and views ---
--#COMMENT --- (of the tables), which display (to ---
--#COMMENT --- the user) the current state of the ---
--#COMMENT --- underlying tables. ---
--#COMMENT --- ---
--#COMMENT --- When the user updates the data in ---
--#COMMENT --- a given view, an INSTEAD OF trigger ---
--#COMMENT --- is invoked to turn the change into an ---
--#COMMENT --- INSERT on the underlying table. All ---
--#COMMENT --- of this is totally transparent to ---
--#COMMENT --- the user. ---
--#COMMENT --- ---
--#COMMENT --- This general design is very useful ---
--#COMMENT --- for those applications that do not ---
--#COMMENT --- have too much data (i.e. table sizes ---
--#COMMENT --- < ten million rows) and not too much ---
--#COMMENT --- activity (< 50,000 changes/day), and ---
--#COMMENT --- which have the following needs: ---
--#COMMENT --- ---
--#COMMENT --- - Need to track (and hold) every ---
--#COMMENT --- change that ever happens. ---
--#COMMENT --- - Need to create many copies of the ---
--#COMMENT --- real world for what-if analysis. ---
--#COMMENT --- ---
--#COMMENT --- This general design may NOT perform ---
--#COMMENT --- very efficiently if a single item ---
--#COMMENT --- (e.g. customer row) is updated many ---
--#COMMENT --- hundreds of times. DB2 will have to ---
--#COMMENT --- search all of the rows, to find the ---
--#COMMENT --- one which has NOT been replaced. ---
--#COMMENT --- ---
--#COMMENT --- ---
--#COMMENT --- VERSIONS ---
--#COMMENT --- ---
--#COMMENT --- This table design supports multiple ---
--#COMMENT --- (up to one billion) versions, where ---
--#COMMENT --- a version is a virtual copy of the ---
--#COMMENT --- real world. Version zero is deemed ---
--#COMMENT --- to be reality. ---
--#COMMENT --- ---
--#COMMENT --- To create a new version, one inserts ---
--#COMMENT --- a row into the VERSION table - always ---
--#COMMENT --- with a begin-timestamp that is less ---
--#COMMENT --- than or equal to the current time. ---
--#COMMENT --- A new version automatically includes ---
--#COMMENT --- (points to) all of the existing data ---
--#COMMENT --- in version zero (reality) - up to ---
--#COMMENT --- the version cutoff time. ---
--#COMMENT --- ---
--#COMMENT --- Changes to the data in a version do ---
--#COMMENT --- not impact the data in version zero ---
--#COMMENT --- (reality), nor in any other version. ---
--#COMMENT --- Likewise, changes in version zero ---
--#COMMENT --- that happen after the version cutoff ---
--#COMMENT --- time do not show up in the version. ---
--#COMMENT --- ---
--#COMMENT --- As written, a (non-zero) version can ---
--#COMMENT --- only be deleted if no data rows were ---
--#COMMENT --- entered (i.e. it is a read-only ---
--#COMMENT --- version). This rule can be changed ---
--#COMMENT --- (to cascading deletes) if desired. ---
--#COMMENT --- ---
--#COMMENT --- ---
--#COMMENT --- USER PROFILE ---
--#COMMENT --- ---
--#COMMENT --- To use the data tables, the user has ---
--#COMMENT --- to insert a row into the user profile ---
--#COMMENT --- table, saying what version they are ---
--#COMMENT --- currently using, and then access the ---
--#COMMENT --- tables via the provided view. ---
--#COMMENT --- ---
--#COMMENT --- ---
--#COMMENT --- DBA ISSUES ---
--#COMMENT --- ---
--#COMMENT --- The GRANT statements listed below ---
--#COMMENT --- illustrate the authorities one would, ---
--#COMMENT --- and would not, grant to users of this ---
--#COMMENT --- application. Note that the underlying ---
--#COMMENT --- data tables cannot be changed ---
--#COMMENT --- directly in any way. ---
--#COMMENT --- ---
--#COMMENT --- The complex views used do not support ---
--#COMMENT --- the use of DB2 referential activity ---
--#COMMENT --- between the data tables. One can use ---
--#COMMENT --- triggers to enforce R.I. (see example ---
--#COMMENT --- in PROD_SALES below), but one must be ---
--#COMMENT --- careful as there should only be one ---
--#COMMENT --- INSTEAD OF trigger per view/action. ---
--#COMMENT --- ---
--#COMMENT --- Enforcing uniqueness of the business ---
--#COMMENT --- keys can be, and is done below, using ---
--#COMMENT --- checks in the relevant triggers, but ---
--#COMMENT --- it is not very pretty code. Note that ---
--#COMMENT --- for this general design to work, all ---
--#COMMENT --- tables must have a unique business ---
--#COMMENT --- key (which can change over time). ---
--#COMMENT --- ---
--#COMMENT --- The sample application used is a very ---
--#COMMENT --- simple one that has three tables: ---
--#COMMENT --- - CUSTOMER: One row per customer. ---
--#COMMENT --- - PRODUCT: One row per product. ---
--#COMMENT --- - PROD_SALES: One row per sale of ---
--#COMMENT --- a product to a customer. ---
--#COMMENT --- There are two support tables: ---
--#COMMENT --- - VERSION: One row per version. ---
--#COMMENT --- - PROFILE: One row per user. ---
--#COMMENT --- Each data table has two views: ---
--#COMMENT --- - One with data on the prior row ---
--#COMMENT --- (if any) and current version/user.---
--#COMMENT --- - One that the users access, with ---
--#COMMENT --- business fields only. ---
--#COMMENT --- Each data table has three triggers: ---
--#COMMENT --- - INSERT: Does the insert (plus). ---
--#COMMENT --- - UPDATE: Turns into an insert. ---
--#COMMENT --- - DELETE: Turns into an insert. ---
--#COMMENT --- ---
--#COMMENT ----------------------------------------------------------
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE VERSION TABLE. ---
--#COMMENT --- ---
--#COMMENT --- This table has one row per version. ---
--#COMMENT --- The design shown below is a minimalist ---
--#COMMENT --- edition. One might also want to track ---
--#COMMENT --- who made a version, and when. ---
--#COMMENT --- ---
--#COMMENT --- Version zero (reality) must have a ---
--#COMMENT --- begin timestamp of high-values. All ---
--#COMMENT --- versions must have a begin timestamp ---
--#COMMENT --- that is <= the current timestamp. A ---
--#COMMENT --- trigger checks all this. ---
--#COMMENT --- ---
--#COMMENT --- Updates of the begin-version timestamp ---
--#COMMENT --- field must NOT be allowed, as it could ---
--#COMMENT --- lead to corrupt data. To prevent this, ---
--#COMMENT --- a sample trigger has been provided. ---
--#COMMENT --- ---
--#COMMENT --- Deletes are, in theory, allowed if the ---
--#COMMENT --- version did NOT change any rows in the ---
--#COMMENT --- data tables. This would allow a given ---
--#COMMENT --- version number to be reused. To this ---
--#COMMENT --- end, this table uses R.I. to check all ---
--#COMMENT --- of the related data tables. But such a ---
--#COMMENT --- check is going to be very expensive (if ---
--#COMMENT --- there are many large data tables) so it ---
--#COMMENT --- might be better to just ban deletes, or ---
--#COMMENT --- to do them offline at scheduled times. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TABLE version
(vrsn INTEGER NOT NULL
,vrsn_bgn_ts TIMESTAMP NOT NULL
,CONSTRAINT version1 CHECK(vrsn >= 0)
,CONSTRAINT version2 CHECK(vrsn < 1000000000)
,PRIMARY KEY(vrsn));
COMMIT;
CREATE TRIGGER version_ins
NO CASCADE BEFORE
INSERT ON version
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
SET nnn.vrsn_bgn_ts =
CASE
WHEN (nnn.vrsn = 0
AND nnn.vrsn_bgn_ts <> '9999-12-31-24.00.00')
OR (nnn.vrsn <> 0
AND nnn.vrsn_bgn_ts > CURRENT TIMESTAMP)
THEN RAISE_ERROR('70001','ERROR: Vrsn-TS > current-TS')
ELSE nnn.vrsn_bgn_ts
END;
COMMIT;
CREATE TRIGGER version_upd
NO CASCADE BEFORE
UPDATE ON version
FOR EACH ROW
MODE DB2SQL
SIGNAL SQLSTATE '71000'
SET MESSAGE_TEXT = 'ERROR: Cannot update version row';
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE PROFILE TABLE. ---
--#COMMENT --- ---
--#COMMENT --- This table has one row per user (DB2 ---
--#COMMENT --- USER special register) who uses (reads ---
--#COMMENT --- or changes) the data tables. ---
--#COMMENT --- ---
--#COMMENT --- The VRSN field indicates which version ---
--#COMMENT --- the user is currently using. To change ---
--#COMMENT --- versions, update this field accordingly.---
--#COMMENT --- ---
--#COMMENT --- The VRSN_BGN_TS field has (a copy of) ---
--#COMMENT --- the begin-timestamp for the user's ---
--#COMMENT --- current version. It is maintained ---
--#COMMENT --- automatically using triggers. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TABLE profile
(user_id VARCHAR(10) NOT NULL
,vrsn INTEGER NOT NULL
,vrsn_bgn_ts TIMESTAMP NOT NULL
,CONSTRAINT profile1 FOREIGN KEY(vrsn)
REFERENCES version(vrsn)
ON DELETE RESTRICT
,PRIMARY KEY(user_id));
COMMIT;
CREATE TRIGGER profile_ins
NO CASCADE BEFORE
INSERT ON profile
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
SET nnn.vrsn_bgn_ts = (SELECT vvv.vrsn_bgn_ts
FROM version vvv
WHERE vvv.vrsn = nnn.vrsn);
COMMIT;
CREATE TRIGGER profile_upd
NO CASCADE BEFORE
UPDATE ON profile
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
SET nnn.vrsn_bgn_ts = (SELECT vvv.vrsn_bgn_ts
FROM version vvv
WHERE vvv.vrsn = nnn.vrsn);
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE DATA (+HISTORY) TABLES. ---
--#COMMENT --- ---
--#COMMENT --- The following tables hold the business ---
--#COMMENT --- data, and also a record of all changes ---
--#COMMENT --- to that data. ---
--#COMMENT --- ---
--#COMMENT --- Every table must have a unique business ---
--#COMMENT --- key (e.g. customer-number), which can ---
--#COMMENT --- change over time, and also be deleted ---
--#COMMENT --- and then re-inserted, but which must ---
--#COMMENT --- always be unique (within a version). ---
--#COMMENT --- ---
--#COMMENT --- Each table has (added to it) some extra ---
--#COMMENT --- fields, that record changes: ---
--#COMMENT --- - CUR_TS: Current timestamp (time ---
--#COMMENT --- row was inserted). ---
--#COMMENT --- - CUR_VRSN: Current version that the ---
--#COMMENT --- user is using. ---
--#COMMENT --- - CUR_ACTN: Type of DB2 action that ---
--#COMMENT --- created the row: insert, ---
--#COMMENT --- update, or delete. ---
--#COMMENT --- - CUR_USER: Who changed the row (for ---
--#COMMENT --- auditing purposes only). ---
--#COMMENT --- The next three fields are NULL if the ---
--#COMMENT --- row represents an insert, but point to ---
--#COMMENT --- the previous row for updates & deletes. ---
--#COMMENT --- - PRV_KEY: The key (e.g. customer ---
--#COMMENT --- number) of the previous ---
--#COMMENT --- row. ---
--#COMMENT --- - PRV_TS: Previous row timestamp. ---
--#COMMENT --- - PRV_VRSN: Previous row version. ---
--#COMMENT --- ---
--#COMMENT --- Observe that the tables do NOT have a ---
--#COMMENT --- begin and end timestamp. Instead, each ---
--#COMMENT --- row optionally points back to the one ---
--#COMMENT --- that it replaces. This design enables ---
--#COMMENT --- to have a one-to-many relationship (and ---
--#COMMENT --- thus multiple versions) between any one ---
--#COMMENT --- row, and what happens to it at some ---
--#COMMENT --- future point in time. ---
--#COMMENT --- ---
--#COMMENT --- A secondary index (on previous-key, ts, ---
--#COMMENT --- and version) has been added to make the ---
--#COMMENT --- following views more efficient. ---
--#COMMENT --- ---
--#COMMENT --- Do NOT allow direct updates to these ---
--#COMMENT --- tables. Inserts, updates, and deletes ---
--#COMMENT --- must come through the views provided ---
--#COMMENT --- below, so the INSTEAD OF triggers can ---
--#COMMENT --- act on them. ---
--#COMMENT --- ---
--#COMMENT --- One can remove the foreign-key checks ---
--#COMMENT --- shown below if you ban all deletes on ---
--#COMMENT --- the version table. ---
--#COMMENT --- ---
--#COMMENT --- The cur_actn constraints are just to ---
--#COMMENT --- make sure that my triggers to do not ---
--#COMMENT --- have a boo-boo. Remove if you wish. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TABLE customer_his
(cust# INTEGER NOT NULL
,cust_name CHAR(10) NOT NULL
,cust_mgr CHAR(10)
,cur_ts TIMESTAMP NOT NULL
,cur_vrsn INTEGER NOT NULL
,cur_actn CHAR(1) NOT NULL
,cur_user VARCHAR(10) NOT NULL
,prv_cust# INTEGER
,prv_ts TIMESTAMP
,prv_vrsn INTEGER
,CONSTRAINT customer1 FOREIGN KEY(cur_vrsn)
REFERENCES version(vrsn)
ON DELETE RESTRICT
,CONSTRAINT customer2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(cust#,cur_vrsn,cur_ts));
CREATE INDEX customer_x2 ON customer_his
(prv_cust#
,prv_ts
,prv_vrsn);
CREATE TABLE product_his
(prod# SMALLINT NOT NULL
,prod_name CHAR(10) NOT NULL
,prod_cost DECIMAL(7,2) NOT NULL
,intro_dt DATE NOT NULL
,cur_ts TIMESTAMP NOT NULL
,cur_vrsn INTEGER NOT NULL
,cur_actn CHAR(1) NOT NULL
,cur_user VARCHAR(10) NOT NULL
,prv_prod# SMALLINT
,prv_ts TIMESTAMP
,prv_vrsn INTEGER
,CONSTRAINT product1 FOREIGN KEY(cur_vrsn)
REFERENCES version(vrsn)
ON DELETE RESTRICT
,CONSTRAINT product2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(prod#,cur_vrsn,cur_ts));
CREATE INDEX product_x2 ON product_his
(prv_prod#
,prv_ts
,prv_vrsn);
CREATE TABLE prod_sales_his
(prod# SMALLINT NOT NULL
,cust# INTEGER NOT NULL
,sale_ts TIMESTAMP NOT NULL
,qty_sold SMALLINT NOT NULL
,cur_ts TIMESTAMP NOT NULL
,cur_vrsn INTEGER NOT NULL
,cur_actn CHAR(1) NOT NULL
,cur_user VARCHAR(10) NOT NULL
,prv_prod# SMALLINT
,prv_cust# INTEGER
,prv_sale_ts TIMESTAMP
,prv_ts TIMESTAMP
,prv_vrsn INTEGER
,CONSTRAINT prod_sales1 FOREIGN KEY(cur_vrsn)
REFERENCES version(vrsn)
ON DELETE RESTRICT
,CONSTRAINT prod_sales2 CHECK(cur_actn IN ('I','U','D'))
,PRIMARY KEY(cust#,prod#,sale_ts,cur_vrsn,cur_ts));
CREATE INDEX prod_sales_x2 ON prod_sales_his
(prv_prod#
,prv_cust#
,prv_sale_ts
,prv_ts
,prv_vrsn);
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE DATA VIEWS (LONG FORM). ---
--#COMMENT --- ---
--#COMMENT --- There is one view below for every data ---
--#COMMENT --- table specified above. Each view shows ---
--#COMMENT --- all of the rows in the table that, for ---
--#COMMENT --- the user's current version (and version ---
--#COMMENT --- zero, where applicable) does NOT have a ---
--#COMMENT --- row that points back to them. ---
--#COMMENT --- ---
--#COMMENT --- All inserts, updates, and deletes are, ---
--#COMMENT --- directly or indirectly, done on these ---
--#COMMENT --- views, where the INSTEAD OF triggers ---
--#COMMENT --- then turn them into inserts. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE VIEW customer_vw AS
SELECT *
FROM customer_his hhh
,profile ppp
WHERE ppp.user_id = USER
AND hhh.cur_actn <> 'D'
AND ((ppp.vrsn = 0
AND hhh.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = 0
AND hhh.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = ppp.vrsn))
AND NOT EXISTS
(SELECT *
FROM customer_his nnn
WHERE nnn.prv_cust# = hhh.cust#
AND nnn.prv_ts = hhh.cur_ts
AND nnn.prv_vrsn = hhh.cur_vrsn
AND ((ppp.vrsn = 0
AND nnn.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = 0
AND nnn.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = ppp.vrsn)));
COMMIT;
CREATE VIEW product_vw AS
SELECT *
FROM product_his hhh
,profile ppp
WHERE ppp.user_id = USER
AND hhh.cur_actn <> 'D'
AND ((ppp.vrsn = 0
AND hhh.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = 0
AND hhh.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = ppp.vrsn))
AND NOT EXISTS
(SELECT *
FROM product_his nnn
WHERE nnn.prv_prod# = hhh.prod#
AND nnn.prv_ts = hhh.cur_ts
AND nnn.prv_vrsn = hhh.cur_vrsn
AND ((ppp.vrsn = 0
AND nnn.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = 0
AND nnn.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = ppp.vrsn)));
COMMIT;
CREATE VIEW prod_sales_vw AS
SELECT *
FROM prod_sales_his hhh
,profile ppp
WHERE ppp.user_id = USER
AND hhh.cur_actn <> 'D'
AND ((ppp.vrsn = 0
AND hhh.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = 0
AND hhh.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = ppp.vrsn))
AND NOT EXISTS
(SELECT *
FROM prod_sales_his nnn
WHERE nnn.prv_prod# = hhh.prod#
AND nnn.prv_cust# = hhh.cust#
AND nnn.prv_sale_ts = hhh.sale_ts
AND nnn.prv_ts = hhh.cur_ts
AND nnn.prv_vrsn = hhh.cur_vrsn
AND ((ppp.vrsn = 0
AND nnn.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = 0
AND nnn.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND nnn.cur_vrsn = ppp.vrsn)));
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE DATA VIEWS (SHORT FORM). ---
--#COMMENT --- ---
--#COMMENT --- These views are the ones that the users ---
--#COMMENT --- would normal refer to (to select and/or ---
--#COMMENT --- update). They are the same as the long ---
--#COMMENT --- form views above, except that only the ---
--#COMMENT --- business fields are shown. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE VIEW customer AS
SELECT cust#
,cust_name
,cust_mgr
FROM customer_vw;
CREATE VIEW product AS
SELECT prod#
,prod_name
,prod_cost
,intro_dt
FROM product_vw;
CREATE VIEW prod_sales AS
SELECT prod#
,cust#
,sale_ts
,qty_sold
FROM prod_sales_vw;
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE INSERT TRIGGERS (DATA TABLES). ---
--#COMMENT --- ---
--#COMMENT --- These INSTEAD OF triggers substitute ---
--#COMMENT --- the user's insert with another insert ---
--#COMMENT --- that also populates the current-data ---
--#COMMENT --- fields. ---
--#COMMENT --- ---
--#COMMENT --- To illustrate how it can be done, the ---
--#COMMENT --- triggers also check for non-unique key ---
--#COMMENT --- values and, in the PROD_SALES table, ---
--#COMMENT --- for foreign-key relationships. In the ---
--#COMMENT --- PRODUCT table, the DELETE trigger also ---
--#COMMENT --- does cascading deletes (to PROD_SALES). ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_ins
INSTEAD OF
INSERT ON customer_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
INSERT INTO customer_his VALUES
(nnn.cust#
,nnn.cust_name
,nnn.cust_mgr
,CURRENT TIMESTAMP
,(SELECT vrsn
FROM profile
WHERE user_id = USER)
,CASE
WHEN 0 < (SELECT COUNT(*)
FROM customer
WHERE cust# = nnn.cust#)
THEN RAISE_ERROR('71001','ERROR: Duplicate cust#')
ELSE 'I'
END
,USER
,NULL
,NULL
,NULL);
COMMIT;
CREATE TRIGGER product_ins
INSTEAD OF
INSERT ON product_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
INSERT INTO product_his VALUES
(nnn.prod#
,nnn.prod_name
,nnn.prod_cost
,nnn.intro_dt
,CURRENT TIMESTAMP
,(SELECT vrsn
FROM profile
WHERE user_id = USER)
,CASE
WHEN 0 < (SELECT COUNT(*)
FROM product
WHERE prod# = nnn.prod#)
THEN RAISE_ERROR('71002','ERROR: Duplicate prod#')
ELSE 'I'
END
,USER
,NULL
,NULL
,NULL);
COMMIT;
CREATE TRIGGER prod_sales_ins
INSTEAD OF
INSERT ON prod_sales_vw
REFERENCING NEW AS nnn
FOR EACH ROW
MODE DB2SQL
INSERT INTO prod_sales_his VALUES
(nnn.prod#
,nnn.cust#
,nnn.sale_ts
,nnn.qty_sold
,CURRENT TIMESTAMP
,(SELECT vrsn
FROM profile
WHERE user_id = USER)
,CASE
WHEN 0 < (SELECT COUNT(*)
FROM prod_sales
WHERE prod# = nnn.prod#
AND cust# = nnn.cust#
AND sale_ts = nnn.sale_ts)
THEN RAISE_ERROR('71003','ERROR: Duplicate prod#/cust#/ts')
WHEN 0 = (SELECT COUNT(*)
FROM product
WHERE prod# = nnn.prod#)
THEN RAISE_ERROR('71004','ERROR: No prod# in Product')
WHEN 0 = (SELECT COUNT(*)
FROM customer
WHERE cust# = nnn.cust#)
THEN RAISE_ERROR('71005','ERROR: No cust# in Customer')
ELSE 'I'
END
,USER
,NULL
,NULL
,NULL
,NULL
,NULL);
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE UPDATE TRIGGERS (DATA TABLES). ---
--#COMMENT --- ---
--#COMMENT --- These INSTEAD OF triggers substitute ---
--#COMMENT --- the user's update with an insert that ---
--#COMMENT --- adds in a row that points to the ---
--#COMMENT --- current row. ---
--#COMMENT --- ---
--#COMMENT --- To illustrate how to do it, updates of ---
--#COMMENT --- the cust# (business key column) are not ---
--#COMMENT --- allowed in the CUSTOMER table. ---
--#COMMENT --- ---
--#COMMENT --- If the prod# in PRODUCT is updated, the ---
--#COMMENT --- trigger updates the related rows in the ---
--#COMMENT --- PROD_SALES table accordingly. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_upd
INSTEAD OF
UPDATE ON customer_vw
REFERENCING NEW AS nnn
OLD AS ooo
FOR EACH ROW
MODE DB2SQL
INSERT INTO customer_his VALUES
(nnn.cust#
,nnn.cust_name
,nnn.cust_mgr
,CURRENT TIMESTAMP
,ooo.vrsn
,CASE
WHEN nnn.cust# <> ooo.cust#
THEN RAISE_ERROR('72001','ERROR: Cannot change cust#')
ELSE 'U'
END
,ooo.user_id
,ooo.cust#
,ooo.cur_ts
,ooo.cur_vrsn);
COMMIT;
--#SET DELIMITER !
CREATE TRIGGER product_upd
INSTEAD OF
UPDATE ON product_vw
REFERENCING NEW AS nnn
OLD AS ooo
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO product_his VALUES
(nnn.prod#
,nnn.prod_name
,nnn.prod_cost
,nnn.intro_dt
,CURRENT TIMESTAMP
,ooo.vrsn
,CASE
WHEN nnn.prod# <> ooo.prod#
AND 0 < (SELECT COUNT(*)
FROM product
WHERE prod# = nnn.prod#)
THEN RAISE_ERROR('72002','ERROR: Duplicate prod#')
ELSE 'U'
END
,ooo.user_id
,ooo.prod#
,ooo.cur_ts
,ooo.cur_vrsn);
INSERT INTO prod_sales_his
SELECT nnn.prod#
,sss.cust#
,sss.sale_ts
,sss.qty_sold
,CURRENT TIMESTAMP
,sss.vrsn
,'U'
,sss.user_id
,sss.prod#
,sss.cust#
,sss.sale_ts
,sss.cur_ts
,sss.cur_vrsn
FROM prod_sales_vw sss
WHERE sss.prod# = ooo.prod#
AND ooo.prod# <> nnn.prod#;
END!
--#SET DELIMITER ;
COMMIT;
CREATE TRIGGER prod_sales_upd
INSTEAD OF
UPDATE ON prod_sales_vw
REFERENCING NEW AS nnn
OLD AS ooo
FOR EACH ROW
MODE DB2SQL
INSERT INTO prod_sales_his VALUES
(nnn.prod#
,nnn.cust#
,nnn.sale_ts
,nnn.qty_sold
,CURRENT TIMESTAMP
,nnn.vrsn
,CASE
WHEN (nnn.prod# <> ooo.prod#
OR nnn.cust# <> ooo.cust#
OR nnn.sale_ts <> ooo.sale_ts)
AND 0 < (SELECT COUNT(*)
FROM prod_sales
WHERE prod# = nnn.prod#
AND cust# = nnn.cust#
AND sale_ts = nnn.sale_ts)
THEN RAISE_ERROR('72003','ERROR: Duplicate prod#/cust#/ts')
WHEN 0 = (SELECT COUNT(*)
FROM product
WHERE prod# = nnn.prod#)
THEN RAISE_ERROR('72004','ERROR: No prod# in Product')
WHEN 0 = (SELECT COUNT(*)
FROM customer
WHERE cust# = nnn.cust#)
THEN RAISE_ERROR('72005','ERROR: No cust# in Customer')
ELSE 'U'
END
,ooo.user_id
,ooo.prod#
,ooo.cust#
,ooo.sale_ts
,ooo.cur_ts
,ooo.cur_vrsn);
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE DELETE TRIGGERS (DATA TABLES). ---
--#COMMENT --- ---
--#COMMENT --- These INSTEAD OF triggers substitute ---
--#COMMENT --- the user's delete with an insert that ---
--#COMMENT --- adds in a row that points to the ---
--#COMMENT --- current row. The new row is given ---
--#COMMENT --- an CUR_ACTN value of "D", so it will ---
--#COMMENT --- not be visible in the views. ---
--#COMMENT --- ---
--#COMMENT --- If a row is deleted in PRODUCT, then ---
--#COMMENT --- the trigger also deletes the related ---
--#COMMENT --- rows in PROD_SALES. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
CREATE TRIGGER customer_del
INSTEAD OF
DELETE ON customer_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
INSERT INTO customer_his VALUES
(ooo.cust#
,ooo.cust_name
,ooo.cust_mgr
,CURRENT TIMESTAMP
,ooo.vrsn
,'D'
,ooo.user_id
,ooo.cust#
,ooo.cur_ts
,ooo.cur_vrsn);
COMMIT;
--#SET DELIMITER !
CREATE TRIGGER product_del
INSTEAD OF
DELETE ON product_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
BEGIN ATOMIC
INSERT INTO product_his VALUES
(ooo.prod#
,ooo.prod_name
,ooo.prod_cost
,ooo.intro_dt
,CURRENT TIMESTAMP
,ooo.vrsn
,'D'
,ooo.user_id
,ooo.prod#
,ooo.cur_ts
,ooo.cur_vrsn);
INSERT INTO prod_sales_his
SELECT sss.prod#
,sss.cust#
,sss.sale_ts
,sss.qty_sold
,CURRENT TIMESTAMP
,sss.vrsn
,'D'
,sss.user_id
,sss.prod#
,sss.cust#
,sss.sale_ts
,sss.cur_ts
,sss.cur_vrsn
FROM prod_sales_vw sss
WHERE sss.prod# = ooo.prod#;
END!
--#SET DELIMITER ;
COMMIT;
CREATE TRIGGER prod_sales_del
INSTEAD OF
DELETE ON prod_sales_vw
REFERENCING OLD AS ooo
FOR EACH ROW
MODE DB2SQL
INSERT INTO prod_sales_his VALUES
(ooo.prod#
,ooo.cust#
,ooo.sale_ts
,ooo.qty_sold
,CURRENT TIMESTAMP
,ooo.vrsn
,'D'
,ooo.user_id
,ooo.prod#
,ooo.cust#
,ooo.sale_ts
,ooo.cur_ts
,ooo.cur_vrsn);
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- GRANT AUTHORITIES ON TABLES & VIEWS. ---
--#COMMENT --- ---
--#COMMENT --- Below is a list of typical access that ---
--#COMMENT --- one would allow general users in this ---
--#COMMENT --- application. Note especially that NO ---
--#COMMENT --- direct insert/update/delete access is ---
--#COMMENT --- is given on the underlying data tables. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
GRANT SELECT ON version TO PUBLIC;
GRANT INSERT ON version TO PUBLIC;
GRANT DELETE ON version TO PUBLIC;
GRANT SELECT ON profile TO PUBLIC;
GRANT INSERT ON profile TO PUBLIC;
GRANT UPDATE ON profile TO PUBLIC;
GRANT DELETE ON profile TO PUBLIC;
GRANT SELECT ON customer TO PUBLIC;
GRANT INSERT ON customer TO PUBLIC;
GRANT UPDATE ON customer TO PUBLIC;
GRANT DELETE ON customer TO PUBLIC;
GRANT SELECT ON product TO PUBLIC;
GRANT INSERT ON product TO PUBLIC;
GRANT UPDATE ON product TO PUBLIC;
GRANT DELETE ON product TO PUBLIC;
GRANT SELECT ON prod_sales TO PUBLIC;
GRANT INSERT ON prod_sales TO PUBLIC;
GRANT UPDATE ON prod_sales TO PUBLIC;
GRANT DELETE ON prod_sales TO PUBLIC;
GRANT SELECT ON customer_his TO PUBLIC;
GRANT SELECT ON product_his TO PUBLIC;
GRANT SELECT ON prod_sales_his TO PUBLIC;
COMMIT;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- CREATE VERSION/PROFILE ROWS. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
INSERT INTO version VALUES(0,'9999-12-31-24.00.00');
INSERT INTO profile (user_id,vrsn) VALUES(USER,0);
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- BAD VERSION, FAIL ---
--#COMMENT ----------------------------
INSERT INTO version VALUES(1,CURRENT TIMESTAMP + 1 DAY);
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- SELECT FROM TABLES---
--#COMMENT ----------------------------
SELECT *
FROM version
ORDER BY vrsn;
SELECT *
FROM profile
ORDER BY user_id;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- RUN SOME TESTS ON CUSTOMER. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
INSERT INTO customer VALUES(001,'ABC','MMM');
INSERT INTO customer VALUES(002,'DEF','MMM');
INSERT INTO customer VALUES(003,'ERT','MMM');
UPDATE customer
SET cust_name = '222'
WHERE cust# = 2;
COMMIT;
SELECT *
FROM customer_vw
ORDER BY cust#;
UPDATE customer
SET cust_name = '333'
WHERE cust# >= 2;
INSERT INTO customer VALUES(004,'FGH','YYY');
SELECT *
FROM customer_vw
ORDER BY cust#;
DELETE
FROM customer
WHERE cust# IN (1,3,2);
INSERT INTO customer VALUES(002,'A22','M22');
INSERT INTO customer VALUES(007,'A77','M77');
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- SEE CURRENT DATA ---
--#COMMENT ----------------------------
SELECT *
FROM customer
ORDER BY cust#;
SELECT *
FROM customer_vw
ORDER BY cust#;
SELECT *
FROM customer_his
ORDER BY cur_ts
,cust#;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- SEE PAST DATA ---
--#COMMENT ----------------------------
INSERT INTO version VALUES
(1
,(SELECT cur_ts + 1 MICROSECOND
FROM customer_his
WHERE cust# = 4));
UPDATE profile
SET vrsn = 1
WHERE user_id = USER;
SELECT *
FROM version
ORDER BY vrsn;
SELECT *
FROM profile
ORDER BY user_id;
SELECT *
FROM customer
ORDER BY cust#;
SELECT *
FROM customer_vw
ORDER BY cust#;
COMMIT;
--#COMMENT ----------------------------
--#COMMENT --- CHANGE ROW IN VRSN---
--#COMMENT ----------------------------
UPDATE customer
SET cust_name = '771'
WHERE cust# = 2;
UPDATE customer
SET cust_name = '772'
,cust_mgr = 'M72'
WHERE cust# = 2;
SELECT *
FROM customer_vw
ORDER BY cust#;
UPDATE profile
SET vrsn = 0
WHERE user_id = USER;
SELECT *
FROM customer_vw
ORDER BY cust#;
SELECT *
FROM customer_his
ORDER BY cur_ts
,cust#;
COMMIT;
--#COMMENT ----------------------------
--#COMMENT --- INSERT DUP. ROW ---
--#COMMENT ----------------------------
INSERT INTO customer VALUES(002,'DDD','DDD');
COMMIT;
SELECT *
FROM customer_vw
ORDER BY cust#;
--#COMMENT ----------------------------
--#COMMENT --- UPDATE ROW ---
--#COMMENT ----------------------------
UPDATE customer
SET cust# = 7
WHERE cust# = 2;
COMMIT;
SELECT *
FROM customer_vw
ORDER BY cust#;
--#COMMENT ----------------------------
--#COMMENT --- TRY TO DELETE VRSN---
--#COMMENT --- THAT HAS OWN DATA ---
--#COMMENT --- (SHOULD FAIL). ---
--#COMMENT ----------------------------
DELETE
FROM version
WHERE vrsn = 1;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- CREATE TEMP VRSN ---
--#COMMENT --- TO SEE OLD DATA ---
--#COMMENT --- (NO CHANGES). ---
--#COMMENT ----------------------------
INSERT INTO version VALUES
(2
,(SELECT cur_ts + 1 MICROSECOND
FROM customer_his
WHERE cust# = 4
AND cur_actn = 'I'));
UPDATE profile
SET vrsn = 2
WHERE user_id = USER;
SELECT *
FROM version
ORDER BY vrsn;
SELECT *
FROM profile
ORDER BY user_id;
SELECT *
FROM customer
ORDER BY cust#;
SELECT *
FROM customer_vw
ORDER BY cust#;
UPDATE profile
SET vrsn = 0
WHERE user_id = USER;
DELETE
FROM version
WHERE vrsn = 2;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- TRACE "2" CHANGES ---
--#COMMENT --- (IN VRSN 0 ONLY) ---
--#COMMENT ----------------------------
WITH temp1 (cust#, cur_ts) AS
(SELECT cust#
,MIN(cur_ts)
FROM customer_his
WHERE cust# = 2
AND cur_vrsn = 0
GROUP BY cust#
UNION ALL
SELECT hhh.cust#
,hhh.cur_ts
FROM temp1 ttt
,customer_his hhh
WHERE ttt.cust# = hhh.prv_cust#
AND ttt.cur_ts = hhh.prv_ts
AND hhh.cur_vrsn = 0)
SELECT hhh.*
FROM temp1 ttt
,customer_his hhh
WHERE ttt.cust# = hhh.cust#
AND ttt.cur_ts = hhh.cur_ts
AND hhh.cur_vrsn = 0
ORDER BY hhh.cur_ts;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- TRACE "2" CHANGES ---
--#COMMENT --- (IN CURRENT VRSN) ---
--#COMMENT ----------------------------
UPDATE profile
SET vrsn = 1
WHERE user_id = USER;
WITH temp1 (cust#, cur_ts, cur_vrsn) AS
(SELECT hhh.cust#
,MIN(hhh.cur_ts)
,hhh.cur_vrsn
FROM customer_his hhh
,profile ppp
WHERE hhh.cust# = 2
AND ppp.user_id = USER
AND ((ppp.vrsn = 0
AND hhh.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = 0
AND hhh.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = ppp.vrsn))
GROUP BY hhh.cust#
,hhh.cur_vrsn
UNION ALL
SELECT hhh.cust#
,hhh.cur_ts
,hhh.cur_vrsn
FROM temp1 ttt
,customer_his hhh
,profile ppp
WHERE ttt.cust# = hhh.prv_cust#
AND ttt.cur_ts = hhh.prv_ts
AND ttt.cur_vrsn = hhh.prv_vrsn
AND ((ppp.vrsn = 0
AND ppp.user_id = USER
AND hhh.cur_vrsn = 0)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = 0
AND hhh.cur_ts < ppp.vrsn_bgn_ts)
OR (ppp.vrsn > 0
AND hhh.cur_vrsn = ppp.vrsn)))
SELECT hhh.*
FROM temp1 ttt
,customer_his hhh
WHERE ttt.cust# = hhh.cust#
AND ttt.cur_ts = hhh.cur_ts
AND ttt.cur_vrsn = hhh.cur_vrsn
ORDER BY hhh.cur_ts;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- RUN SOME VOLUME TESTS ON PRODUCT. ---
--#COMMENT --- ---
--#COMMENT --- On my desktop DB2 system, without any ---
--#COMMENT --- tuning, I am able to get the following ---
--#COMMENT --- performance rates (for 10K rowss): ---
--#COMMENT --- - INSERT: 600 rows per second. ---
--#COMMENT --- - UPDATE: 700 rows per second. ---
--#COMMENT --- - DELETE: 850 rows per second. ---
--#COMMENT --- ---
--#COMMENT --- I do not have any secondary (business) ---
--#COMMENT --- indexes, and nor have my test rows been ---
--#COMMENT --- updated many times, so your results may ---
--#COMMENT --- vary. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- INSERT 10K ROWS ---
--#COMMENT ----------------------------
UPDATE profile
SET vrsn = 0
WHERE user_id = USER;
COMMIT;
INSERT INTO product
WITH temp1 (n) AS
(VALUES (1)
UNION ALL
SELECT n + 1
FROM temp1
WHERE n < 500)
SELECT n
,CHAR(n)
,n * 1.23
,DATE('2003-10-14') + n DAYS
FROM temp1;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- COMPARE TO INSERT ---
--#COMMENT --- (measure time) ---
--#COMMENT ----------------------------
WITH temp1 (n) AS
(VALUES (1)
UNION ALL
SELECT n + 1
FROM temp1
WHERE n < 500)
SELECT COUNT(*)
,COUNT(DISTINCT n)
FROM temp1;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- CREATE NEW VRSN ---
--#COMMENT ----------------------------
INSERT INTO version VALUES(3,CURRENT TIMESTAMP);
UPDATE profile
SET vrsn = 3
WHERE user_id = USER;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- UPDATE IN VRSN ---
--#COMMENT ----------------------------
UPDATE product
SET prod_name = '3' CONCAT prod_name
WHERE prod# = prod# /3 * 3;
COMMIT;
DELETE
FROM product
WHERE prod# = prod# /7 * 7;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- UPDATE IN REALITY ---
--#COMMENT ----------------------------
UPDATE profile
SET vrsn = 0
WHERE user_id = USER;
COMMIT;
UPDATE product
SET prod_name = '0' CONCAT prod_name
WHERE prod# = prod# /3 * 3;
COMMIT;
DELETE
FROM product
WHERE prod# = prod# /9 * 9;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- QUERY DATA ---
--#COMMENT ----------------------------
UPDATE profile
SET vrsn = 0
WHERE user_id = USER;
COMMIT;
SELECT *
FROM product
WHERE prod# < 30
ORDER BY prod#;
UPDATE profile
SET vrsn = 3
WHERE user_id = USER;
COMMIT;
SELECT *
FROM product
WHERE prod# < 30
ORDER BY prod#;
SELECT *
FROM product_his
WHERE prod# < 30
ORDER BY prod#
,cur_ts;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- RUN SOME R.I. TESTS USING PROD_SALES. ---
--#COMMENT --- ---
--#COMMENT --- The above INSTEAD OF triggers enforce ---
--#COMMENT --- the following R.I. rules between the ---
--#COMMENT --- PRODUCT and PROD_SALES tables: ---
--#COMMENT --- - Prod# in PROD_SALES must already ---
--#COMMENT --- exist in the PRODUCT table. ---
--#COMMENT --- - Delete of prod# from PRODUCT also ---
--#COMMENT --- cascades down to PROD_SALES. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
UPDATE profile
SET vrsn = 3
WHERE user_id = USER;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- INSERT WILL FAIL ---
--#COMMENT ----------------------------
INSERT INTO prod_sales VALUES (-55,2,CURRENT TIMESTAMP,123);
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- INSERT WILL WORK ---
--#COMMENT ----------------------------
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,123);
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,234);
INSERT INTO prod_sales VALUES (5,2,CURRENT TIMESTAMP,345);
COMMIT;
SELECT *
FROM prod_sales
ORDER BY prod#
,cust#
,sale_ts;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- ORDINARY UPDATE ---
--#COMMENT ----------------------------
UPDATE prod_sales
SET sale_ts = sale_ts + 1 SECOND
WHERE prod# = 5
AND qty_sold = 234;
COMMIT;
SELECT *
FROM prod_sales
ORDER BY prod#
,cust#
,sale_ts;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- ORDINARY DELETE ---
--#COMMENT ----------------------------
DELETE
FROM prod_sales
WHERE prod# = 5
AND qty_sold = 345;
COMMIT;
SELECT *
FROM prod_sales
ORDER BY prod#
,cust#
,sale_ts;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- UPDATE PRODUCT ---
--#COMMENT ----------------------------
UPDATE product
SET prod# = prod# * -1
WHERE prod# = 5;
COMMIT;
SELECT *
FROM prod_sales
ORDER BY prod#
,cust#
,sale_ts;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- CASCADING DELETE ---
--#COMMENT ----------------------------
DELETE
FROM product
WHERE prod# = -5;
COMMIT;
--#COMMENT
--#COMMENT ----------------------------
--#COMMENT --- QUERY DATA ---
--#COMMENT ----------------------------
SELECT *
FROM prod_sales
ORDER BY prod#
,cust#
,sale_ts;
SELECT *
FROM prod_sales_his
ORDER BY cur_ts
,prod#
,cust#
,sale_ts;
SELECT *
FROM product_his
WHERE prod# IN (5,-5)
ORDER BY prod#
,cur_ts;
--#COMMENT
--#COMMENT --------------------------------------------------
--#COMMENT --- ---
--#COMMENT --- DROP OBJECTS. ---
--#COMMENT --- ---
--#COMMENT --------------------------------------------------
DROP TRIGGER customer_ins;
DROP TRIGGER customer_upd;
DROP TRIGGER customer_del;
DROP TRIGGER product_ins;
DROP TRIGGER product_upd;
DROP TRIGGER product_del;
DROP TRIGGER prod_sales_ins;
DROP TRIGGER prod_sales_upd;
DROP TRIGGER prod_sales_del;
COMMIT;
DROP VIEW customer;
DROP VIEW product;
DROP VIEW prod_sales;
DROP VIEW customer_vw;
DROP VIEW product_vw;
DROP VIEW prod_sales_vw;
DROP TABLE customer_his;
DROP TABLE product_his;
DROP TABLE prod_sales_his;
COMMIT;
DROP TRIGGER profile_ins;
DROP TRIGGER profile_upd;
DROP TABLE profile;
COMMIT;
DROP TRIGGER version_ins;
DROP TRIGGER version_upd;
DROP TABLE version;
COMMIT;