[teiid-issues] [JBoss JIRA] (TEIID-5285) Add high-level feature for redirection of updates

[ https://issues.jboss.org/browse/TEIID-5285?page=com.atlassian.jira.plugin... ] Ramesh Reddy commented on TEIID-5285: ------------------------------------- [~shawkins] I started with {code} <vdb name="redirection" version="1"> <!--1-insert, 2-update, 3-delete for status--> <model visible="false" name="master"> <source name="master" translator-name="postgresql" connection-jndi-name="java:/pgMaster"/> <metadata type = "DDL"><![CDATA[ CREATE FOREIGN TABLE CUSTOMER ( SSN varchar(10), FIRSTNAME varchar(64), LASTNAME varchar(64), ST_ADDRESS varchar(256), APT_NUMBER varchar(32), CITY varchar(64), STATE varchar(32), ZIPCODE varchar(10), PHONE varchar(15), PRIMARY KEY(SSN) ) OPTIONS (UPDATABLE 'FALSE'); CREATE FOREIGN TABLE CUSTOMER_MODIFIED ( SSN varchar(10), FIRSTNAME varchar(64), LASTNAME varchar(64), ST_ADDRESS varchar(256), APT_NUMBER varchar(32), CITY varchar(64), STATE varchar(32), ZIPCODE varchar(10), PHONE varchar(15), STATUS integer, PRIMARY KEY(SSN) ) OPTIONS (UPDATABLE 'TRUE'); ]]> </metadata> </model> <model name="whatever" type="VIRTUAL"> <metadata type = "DDL"><![CDATA[ CREATE VIEW customer OPTIONS (UPDATABLE 'TRUE') AS SELECT o.SSN, o.FIRSTNAME, o.LASTNAME, o.PHONE FROM master.CUSTOMER o LEFT OUTER JOIN master.CUSTOMER_MODIFIED m ON (o.SSN=m.SSN) where m.STATUS is null UNION ALL SELECT SSN, FIRSTNAME, LASTNAME, PHONE FROM master.CUSTOMER_MODIFIED where STATUS <> 3; CREATE TRIGGER ON customer INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1); END; CREATE TRIGGER ON customer INSTEAD OF DELETE AS FOR EACH ROW BEGIN ATOMIC UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (OLD.SSN, OLD.FIRSTNAME, OLD.LASTNAME, OLD.PHONE, 3); END; CREATE TRIGGER ON customer INSTEAD OF UPDATE AS FOR EACH ROW BEGIN ATOMIC IF (CHANGING.SSN) BEGIN DECLARE string VARIABLES.X = (SELECT SSN FROM master.CUSTOMER_MODIFIED); IF (VARIABLES.X is NULL) BEGIN UPSERT INTO master.CUSTOMER_MODIFIED (SSN, STATUS) values (OLD.SSN, 3); UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 1); END ELSE BEGIN RAISE SQLEXCEPTION 'duplicate key'; END END ELSE BEGIN UPSERT INTO master.CUSTOMER_MODIFIED (SSN, FIRSTNAME, LASTNAME, PHONE, STATUS) values (NEW.SSN, NEW.FIRSTNAME, NEW.LASTNAME, NEW.PHONE, 2); END END; ]]> </metadata> </model> </vdb> {code} and executed {code} delete from customer where SSN = 'CST01006' insert into customer (SSN, FIRSTNAME, LASTNAME, PHONE) values ('1111', 'Ramesh', 'Reddy', '(314)-555-1212'); update customer set FIRSTNAME = 'Bob' where SSN = 'CST01035'; update customer set FIRSTNAME = 'Henry' where SSN = 'CST01035'; {code} When it came to second update it was not taking place. Not sure, what needs to be changed? -- This message was sent by Atlassian JIRA (v7.5.0#75005)