Differences

This shows you the differences between two versions of the page.

--- jvx:server:storage:insteadof [2018/02/07 08:14]
admin created
+++ jvx:server:storage:insteadof [2020/06/29 13:12] (current)
cduncan Edited for English grammar (capitalization, punctuation, correct verb conjugation)
@@ Line 2: / Line 2: @@
 ~~Title: InsteadOf Trigger~~
-If you're an Oracle user, you'll know that it's possible to update views with insteadOf triggers. An insteadOf trigger will do the CRUD operation(s) and it will be called with new/changed values like any other trigger. It's like a procedure which does CRUD programmatically. It's a simple concept but not available in all databases.
+If you're an Oracle user, you'll know that it's possible to update views with insteadOf triggers. An insteadOf trigger will do the CRUD operation(s), and it will be called with new/changed values like any other trigger. It's like a procedure that does CRUD programatically. It's a simple concept but not available in all databases.
-With JVx' DBStorage it's super easy to use insteadOf triggers in your business logic independent of the database.
+With JVx' DBStorage, it's super easy to use insteadOf triggers in your business logic independent of the database.
-We have a short example for you. Imagine you have an Activity table with columns: name, cost, valid_from, valid_to, ... The table also has a foreign key to a contract. One contract has one or more activities:
+We have a short example for you. Imagine you have an activity table with columns: name, cost, valid_from, valid_to, etc. The table also has a foreign key to a contract. One contract has one or more activities:
 {{:jvx:server:storage:contract_activity_io.png?nolink|}}
-Our GUI shows a list of all available activities and it should be possible to change the contract. If the contract is available, this would be a straight forward implementation because JVx supports everything out-of-the-box. But we want to insert a new contract if it isn't available. Without additional popups, ...
+Our GUI shows a list of all available activities and it should be possible to change the contract. If the contract is available, this would be a straightforward implementation because JVx supports everything out of the box. However, we want to insert a new contract if it isn't available without additional popups.
 {{:jvx:server:storage:insteadofgui.png?nolink|}}
@@ Line 16: / Line 16: @@
 We'll use an insteadOf trigger to solve this problem.
-In above screenshot, the "New contract" wasn't available in the Contract table, but we did the insert during inserting a new Activity. Here's the whole code:
+In the above screenshot, the "New contract" wasn't available in the contract table, but we did the insert during inserting a new activity. Here's the whole code:
 <file java TestInsteadOf.java>
@@ Line 230: / Line 230: @@
 </file>
-The first method allows inserting new contracts, because the cell editor won't check for existing contracts. The second method implements the insteadOf trigger.
+The first method allows inserting new contracts because the cell editor won't check for existing contracts. The second method implements the insteadOf trigger.
 The trigger itself checks if the contract ID is empty and creates a new contract in this case. The activity will be inserted with the new contract id. The eventInsteadOfUpdate is missing in our example, but it works the same way.
@@ Line 236: / Line 236: @@
 You'll find the implementation of SimpleTestLauncher [[jvx:code_snippets#test_ui_with_simple_junit_test|here]].
+The database objects (Oracle):
+<file sql create.sql>
+CREATE TABLE CONTRACT
+(
+  id    NUMBER(16) NOT NULL,
+  title VARCHAR2(1000),
+  state NUMBER(1) DEFAULT 1
+)
+;
+COMMENT ON column CONTRACT.id
+  IS 'PK';
+COMMENT ON column CONTRACT.title
+  IS 'Contract title';
+COMMENT ON column CONTRACT.state
+  IS 'Contract state';
+ALTER TABLE CONTRACT
+  add constraint CONT_PK primary key (ID);
+CREATE TABLE ACTIVITY
+(
+  id                  NUMBER(16) NOT NULL,
+  cont_id             NUMBER(16),
+  name                VARCHAR2(1000),
+  cost                NUMBER(10,2),
+  valid_from          DATE,
+  valid_to            DATE,
+  responsible_user_id NUMBER(16),
+  validated           CHAR(1) DEFAULT 'Y'
+)
+;
+COMMENT ON column ACTIVITY.id
+  IS 'PK';
+COMMENT ON column ACTIVITY.cont_id
+  IS 'Contract FK';
+COMMENT ON column ACTIVITY.name
+  IS 'Activity name';
+COMMENT ON column ACTIVITY.cost
+  IS 'Activity costs';
+COMMENT ON column ACTIVITY.valid_from
+  IS 'Valid from';
+COMMENT ON column ACTIVITY.valid_to
+  IS 'Valid to';
+COMMENT ON column ACTIVITY.responsible_user_id
+  IS 'Responsible User FK';
+COMMENT ON column ACTIVITY.validated
+  IS 'Whether the activity is valid';
+ALTER TABLE ACTIVITY
+  add constraint ACTI_PK primary key (ID);
+ALTER TABLE ACTIVITY
+  add constraint ACTI_CONT_FK foreign key (CONT_ID)
+  references CONTRACT (ID);
+ALTER TABLE ACTIVITY
+  add constraint ACTI_RESP_USER_FK foreign key (RESPONSIBLE_USER_ID)
+  references USERS (ID);
+CREATE sequence SEQ_ACTIVITY_ID
+minvalue 1
+maxvalue 999999999999999999999999999
+START WITH 41
+increment BY 1
+cache 20
+ORDER;
+CREATE sequence SEQ_CONTRACT_ID
+minvalue 1
+maxvalue 999999999999999999999999999
+START WITH 21
+increment BY 1
+cache 20
+ORDER;
+CREATE OR REPLACE force VIEW v_activities AS
+SELECT a.id
+      ,a.cont_id
+      ,c.title cont_title
+      ,a.name
+      ,a.cost
+      ,a.valid_from
+      ,a.valid_to
+  FROM activity a
+      ,contract c
+ WHERE a.cont_id = c.id;
+CREATE OR REPLACE TRIGGER TR_ACTIVITY_BR_IU
+  before INSERT OR UPDATE ON activity
+  FOR each ROW
+BEGIN
+  IF (:NEW.id IS NULL) THEN
+    SELECT seq_activity_id.NEXTVAL INTO :NEW.id FROM dual;
+  END IF;
+  IF (:NEW.validated = 'N') THEN
+    IF (:NEW.valid_from IS NULL) THEN
+      raise_application_error(-20000, 'Valid from can''t be null!');
+    END IF;
+  END IF;
+END TR_ACTIVITY_BR_IU;
+/
+CREATE OR REPLACE TRIGGER TR_CONTRACT_BR_IU
+  before INSERT OR UPDATE ON contract
+  FOR each ROW
+BEGIN
+  IF (:NEW.id IS NULL) THEN
+    SELECT seq_contract_id.NEXTVAL INTO :NEW.id FROM dual;
+  END IF;
+  IF (:NEW.state = 4) THEN
+    UPDATE activity
+       SET validated = 'N'
+     WHERE cont_id = id
+       AND validated = 'Y';
+  END IF;
+END TR_CONTRACT_BR_IU;
+/
+prompt Disabling triggers FOR CONTRACT...
+ALTER TABLE CONTRACT disable ALL triggers;
+prompt Disabling triggers FOR ACTIVITY...
+ALTER TABLE ACTIVITY disable ALL triggers;
+prompt Disabling foreign key constraints FOR ACTIVITY...
+ALTER TABLE ACTIVITY disable constraint ACTI_CONT_FK;
+ALTER TABLE ACTIVITY disable constraint ACTI_RESP_USER_FK;
+prompt Loading CONTRACT...
+INSERT INTO CONTRACT (id, title, state)
+VALUES (1, 'Air', 1);
+COMMIT;
+prompt 1 records loaded
+prompt Loading ACTIVITY...
+INSERT INTO ACTIVITY (id, cont_id, name, cost, valid_from, valid_to, responsible_user_id, validated)
+VALUES (1, 1, 'Part 1', .2, NULL, NULL, NULL, 'Y');
+COMMIT;
+prompt 1 records loaded
+prompt Enabling foreign key constraints FOR ACTIVITY...
+ALTER TABLE ACTIVITY enable constraint ACTI_CONT_FK;
+ALTER TABLE ACTIVITY enable constraint ACTI_RESP_USER_FK;
+prompt Enabling triggers FOR CONTRACT...
+ALTER TABLE CONTRACT enable ALL triggers;
+prompt Enabling triggers FOR ACTIVITY...
+ALTER TABLE ACTIVITY enable ALL triggers;
+</file>