Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 10.0.1 » SQL Anywhere Server - SQL Usage » Using Procedures, Triggers, and Batches » Introduction to triggers » Advanced information on triggers

Advanced information on triggers Next Page

INSTEAD OF triggers


INSTEAD OF triggers differ from BEFORE and AFTER triggers because when an INSTEAD OF trigger fires, the triggering action is skipped and the specified action is performed instead.

The following is a list of capabilities and restrictions that are unique to INSTEAD OF triggers:

Updating non-updatable views using INSTEAD OF triggers

INSTEAD OF triggers allow you to execute INSERT, UPDATE, or DELETE statements against a view that is not inherently updatable. The body of the trigger defines what it means to execute the corresponding statement. For example, suppose you create the following view:

CREATE VIEW V1 ( Surname, GivenName, State ) AS
  SELECT DISTINCT Surname, GivenName, State FROM Contacts;

You cannot delete rows from V1 because the DISTINCT keyword makes V1 not inherently updatable. In other words, the database server cannot unambiguously determine what it means to delete a row from V1. However, you could define an INSTEAD OF DELETE trigger that implements a delete operation on V1. For example, the following trigger deletes all rows from Contacts with a given Surname, GivenName, and State when that row is deleted from V1:

CREATE TRIGGER V1_Delete
  INSTEAD OF DELETE ON V1
  REFERENCING OLD AS old_row
  FOR EACH ROW
BEGIN
    DELETE FROM Contacts
      WHERE Surname = old_row.Surname
        AND GivenName = old_row.GivenName
        AND State = old_row.State
END;

Once the V1_Delete trigger is defined, it becomes possible to delete rows from V1. Other INSTEAD OF triggers could be defined to allow INSERT and UPDATE statements to be performed on V1, as well.

If a view with an INSTEAD OF DELETE trigger is nested in another view, it will be treated like a base table for the purposes of checking updatability for a DELETE. The same is true for INSERT and UPDATE. Continuing from our previous example, suppose you create another view:

CREATE VIEW V2 ( Surname, GivenName ) AS
  SELECT Surname, GivenName from V1;

Without the V1_Delete trigger, it is not possible to delete rows from V2. This is because V1 is not inherently updatable, so neither is V2. However, with an INSTEAD OF DELETE trigger defined on V1, it becomes possible to delete rows from V2. Each row deleted from V2 results in a row being deleted from V1, which causes the V1_Delete trigger to fire.

It is important to be aware of any INSTEAD OF triggers defined on nested views, since the firing of those triggers may have unintended consequences. To make the intended behavior explicit, it is considered good practice in cases like these to define INSTEAD OF triggers on any view referencing the nested view. In our example, the following trigger could be defined on V2 to implement the desired behavior for a DELETE statement:

CREATE TRIGGER V2_Delete
  INSTEAD OF DELETE ON V2
  REFERENCING OLD AS old_row
  FOR EACH ROW
BEGIN
    DELETE FROM Contacts
      WHERE Surname = old_row.Surname
        AND GivenName = old_row.GivenName
END;

The V2_Delete trigger ensures that the behavior of a DELETE on V2 remains the same, even if the INSTEAD OF DELETE trigger on V1 is removed or changed.