Advantage Developer Zone

 
 
 

Using Triggers with Database Views

Monday, November 20, 2006

A database view is a virtual table that is defined within your database as an SQL statement. Views can be used just like a table by your application. However, views fall under the same rules as other SQL statements and may not always produce results that can be modified. Triggers can be defined on views to allow the underlying tables to be updated by your client applications. This also gives you the advantage of controlling how data is manipulated within your database.

A Closer Look at Views

Views are essentially an SQL statement that is stored within your database. They are very useful for limiting access to fields, providing calculation, performing complex joins, and allowing queries on queries. For this article, we will focus on joining tables since this generates a static (unchangeable) result.

When a view maps back to a single table, it can be manipulated in the same way you would work with the table itself. This type of view is typically used to limit the amount of information that is sent to the user. For example, a regional view may be created for specific sales people. The view would only display the customers within a specific region to limit the amount of data presented to the user. Since this type of view accesses a single table without any calculations, it can be updated without any additional steps.

If you are using a view that joins one or more tables together, the resulting data is static. Considering more than one table is involved in the view, you must define how the tables are to be updated. This is done using Instead Of triggers which can be defined for the Insert, Update, and Delete events on the view.

Defining the CustomerDetails View

Many applications have the need to store customer information. As part of the normalization process, this customer information may be separated into several tables. In this example, we have a customer table which is linked to an address table using a link table. The three tables are used since a customer can have one or more addresses and each address can be linked to one or more customers. The diagram below displays this relationship:

This model works well for data normalization but it makes it more difficult to display to the users within your application because several tables are involved. To resolve this problem, we will create a view that will display the customer information along with one address. The customer table has a field called DisplayAddress which defines which address to display for the customer. We will call this View CustomerDetails which is defined by the following SQL statement:

SELECT c.CustID, c.LastName, c.FirstName, c.Gender, c.CustomerSince,
      a
.Address1, a.City, a.State, a.ZipCode, c.WorkPhone, c.CellPhone,
      cm
.CompanyName, c.DisplayAddress, c.CompanyID
  
FROM Customer c INNER JOIN Address a ON c.DisplayAddress = a.ID
                  
INNER JOIN Company cm ON c.CompanyID = cm.CompanyID

 

Creating the Update Trigger

Since the CustomerDetails view joins several tables together, the view is static. In order to be able to update the view, triggers need to be added. Advantage has four types of triggers; Before, After, Instead Of, and On Conflict. Only Instead Of triggers can be defined on a view (more information on triggers is available in other tech tips, online seminars, and in the Advantage Help File).

Triggers can be created on a view using Advantage Data Architect (ARC) or via an SQL Create Trigger statement. We will use ARC to create our update trigger.

  1. To access the Trigger window in ARC, right-click on the CustomerDetails view and choose Triggers.

  2. Set the Trigger Type to Instead Of and the Event Type to Update.

  3. Leave the priority set to 1 since only one Instead of trigger can be defined on each event type.

  4. Ensure that the Script tab is selected and type in the following script:

    UPDATE Customer SET CustID = n.CustID, LastName = n.LastName, FirstName = n.FirstName, Gender = n.Gender, CustomerSince = n.CustomerSince, CellPhone = n.CellPhone, WorkPhone = n.WorkPhone,   CompanyID = n.CompanyID, DisplayAddress = n.DisplayAddress
    FROM Customer c, __new n, __old o
    WHERE c.CustID =  o.CustID;

    UPDATE Address SET Address1 = n.Address1, City = n.City, State = n.State, ZipCode = n.ZipCode
    FROM Address a, __new n
    WHERE a.ID = n.DisplayAddress;

  5. Click the Verify Syntax button to check the SQL syntax. Click OK to close the success dialog. If you get an error message verify your SQL statement.

  6. Under Options select "Include Values…" and "Use implicit transactions…". There are no memo or BLOB fields in the affected tables so this option is not necessary. The completed trigger is displayed below.

  7. Click OK to save the trigger. Type "Detail_Update" in the name field and press OK.

The first SQL statement updates the Customer table using the new data passed in from the client. The second SQL statement updates the Address table using the DisplayAddress field value to locate the correct record.

 

Inserting and Deleting Records

With the Update Trigger defined, existing records displayed by the view can now be modified. However, we must create an Insert and Delete trigger to allow new records and removing old records.

For inserting a new record, we will update the Customer table and the AddressLink table. In this case, we will assume that the user will select an Address from a list for the new customer. Once again we will use two SQL statements, one to insert a record into the Customer table and another to add a record to the AddressLink table. The insert requires that we generate a unique CustomerID so a more complex script is used for this statement, shown below.

CREATE TRIGGER Detail_Insert ON CustomerDetails INSTEAD OF INSERT
BEGIN
  
-- Insert a new record into the Customer Table
  
DECLARE CustInfo CURSOR as SELECT * FROM __new;
  
  
-- Open the __old table
  
OPEN CustInfo;
  
FETCH CustInfo;
  
  
-- Add a record to the customer table
  
INSERT INTO Customer (CustID, LastName, FirstName, Gender, CustomerSince,
   WorkPhone, CellPhone, CompanyID, DisplayAddress)
   VALUES (@iCust, CustInfo.LastName, CustInfo.FirstName,CustInfo.Gender,
     CustInfo.CustomerSince, CustInfo.WorkPhone, CustInfo.CellPhone,
     CustInfo
.CompanyID, CustInfo.DisplayAddress);

  
-- Add a record to the Address Link table
  
-- Assume it is a work address
  
INSERT INTO AddressLink (AddrID, OwnerID, AddrType)
    
VALUES (old.DisplayAddress, @iCust, 'A1');

END
PRIORITY 1;

Similarly, the Delete Trigger will remove a record from the Customer table and all links from the AddressLink table. It will not remove any records from the Address table since they may be linked to other customers. The following statement will create the Delete trigger on the CustomerDetails view.

CREATE TRIGGER Detail_Delete ON CustomerDetails INSTEAD OF DELETE
BEGIN
  
-- Delete the customer from the Customer Table
  
DELETE FROM Customer WHERE CustID IN (SELECT CustID FROM __old);

  
-- Delete all the address references in the Address Link table
  
DELETE FROM AddressLink WHERE OwnerID IN (SELECT CustID FROM_old);
END

Summary

Views are a powerful mechanism for customizing the way your data is presented to users. They can be used to enhance security and streamline complex table relationships. Views that create static results can still be modified using triggers. These triggers allow you to define how the underlying tables are modified ensuring your data integrity.