InsteadOf Trigger

picture_as_pdf

This is an old revision of the document!

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.

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:

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, …

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:

TestInsteadOf.java

/*
 * Copyright 2016 SIB Visions GmbH
 * 
 * Licensed under the Apache License, Version 2.0 (the "License"); you may not
 * use this file except in compliance with the License. You may obtain a copy of
 * the License at
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS, WITHOUT
 * WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. See the
 * License for the specific language governing permissions and limitations under
 * the License.
 *
 *
 * History
 *
 * 11.08.2016 - [JR] - creation
 */
package com.sibvisions.forum;
 
import javax.rad.application.SimpleTestLauncher;
import javax.rad.genui.UIFactoryManager;
import javax.rad.genui.container.UIPanel;
import javax.rad.genui.layout.UIBorderLayout;
import javax.rad.type.bean.IBean;
import javax.rad.ui.celleditor.ILinkedCellEditor;
 
import org.junit.Before;
import org.junit.BeforeClass;
import org.junit.Test;
 
import com.sibvisions.apps.components.NavigationTable;
import com.sibvisions.apps.server.util.LifeCycleUtil;
import com.sibvisions.rad.persist.StorageDataBook;
import com.sibvisions.rad.persist.event.IStorageListener;
import com.sibvisions.rad.persist.event.StorageEvent;
import com.sibvisions.rad.persist.jdbc.DBAccess;
import com.sibvisions.rad.persist.jdbc.DBStorage;
import com.sibvisions.rad.ui.swing.impl.SwingFactory;
import com.sibvisions.util.type.CommonUtil;
 
public class TestInsteadOf extends UIPanel
{
    /** the database access. */
    private DBAccess dba;
 
    /** the contract storage. */
    private DBStorage dbsContract;
    /** the activity storage. */
    private DBStorage dbsActivity;
 
    /**
     * Initializes the unit test.
     * 
     * @throws Exception if initialization fails
     */
    @BeforeClass
    public static void beforeClass()
    {
        UIFactoryManager.getFactoryInstance(SwingFactory.class);
    }
 
    /**
     * Initializes the test case.
     * 
     * @throws Exception if initialization fails
     */
    @Before
    public void before() throws Exception
    {
        dba = DBAccess.getDBAccess("jdbc:oracle:thin:@localhost:1521:XE", 
                                   "username", "password");
        dba.open();
    }
 
    /**
     * Cleanup the test case.
     */
    @After
    public void after() 
    {
        CommonUtil.close(dbsActivity, dbsContract, dba);
    }
 
    /**
     * Tests application start with a custom UI.
     * 
     * @throws Exception if app start fails
     */
    @Test
    public void testInsteadOf() throws Exception
    {
        DBStorage dbsActivities = new DBStorage();
        dbsActivities.setAutoLinkReference(false);
        dbsActivities.setFromClause("V_ACTIVITIES");
        dbsActivities.setWritebackTable("ACTIVITY");
        //manual autolink
        dbsActivities.createAutomaticLinkReference(new String[] {"CONT_ID", "CONT_TITLE"}, 
                                                   "CONTRACT", 
                                                   new String[] {"ID", "TITLE"});
        dbsActivities.eventInsteadOfInsert().addListener(new IStorageListener()
        {
            public void storageChanged(StorageEvent pStorageEvent) throws Throwable
            {
                boolean bAutoCommit = dba.isAutoCommit();
 
                try
                {
                    dba.setAutoCommit(false);
 
                    DBStorage contract = getContract();
 
                    IBean newRecord = pStorageEvent.getNew();
 
                    //check if a new contract should be created for the activity
 
                    if (newRecord.get("CONT_ID") == null)
                    {
                        IBean bnContract = contract.createEmptyBean();
 
                        bnContract.put("TITLE", newRecord.get("CONT_TITLE"));
 
                        bnContract = contract.insert(bnContract);
 
                        //we need the new id in our activity record!
                        newRecord.put("CONT_ID", bnContract.get("ID"));
                    }
 
                    //updates newRecord, because we need the updated columns (e.g. ID)
                    newRecord.putAll(getActivity().insert(newRecord));
 
                    dba.commit();
                }
                catch (Throwable th)
                {
                    dba.rollback();
 
                    throw th;
                }
                finally
                {
                    dba.setAutoCommit(bAutoCommit);
                }
            }
        });
        dbsActivities.setDBAccess(dba);
        dbsActivities.open();
 
        StorageDataBook sdbActivities = new StorageDataBook(dbsActivities);
        sdbActivities.open();
 
        ((ILinkedCellEditor)sdbActivities.getRowDefinition().getColumnDefinition("CONT_TITLE").
                                  getDataType().getCellEditor()).setValidationEnabled(false);
 
        setLayout(new UIBorderLayout());
 
        NavigationTable nav = new NavigationTable();
        nav.setDataBook(sdbActivities);
        //show all columns
        //nav.setColumnView(new ColumnView(sdbActivities.getRowDefinition().getColumnNames()));
 
        add(nav);
 
        new SimpleTestLauncher(this);
    }
 
    /**
     * Gets the contract storage.
     * 
     * @return the contract storage.
     * @throws Exception if creation fails
     */
    public DBStorage getContract() throws Exception
    {
        if (dbsContract == null)
        {
            dbsContract = LifeCycleUtil.createWriteBackStorage(dba, "CONTRACT");
        }
 
        return dbsContract;
    }
 
    /**
     * Gets the activity storage.
     * 
     * @return the contract storage.
     * @throws Exception if creation fails
     */
    public DBStorage getActivity() throws Exception
    {
        if (dbsActivity == null)
        {
            dbsActivity = LifeCycleUtil.createWriteBackStorage(dba, "ACTIVITY");
        }
 
        return dbsActivity;
    }
 
}   // TestInsteadOf

The important things are:

...setValidationEnabled(false);
 
eventInsteadOfInsert()

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.

You'll find the implementation of SimpleTestLauncher here.