DB2 Instead of Triggers

DB2/400 Instead of TriggersIn March 2005, DB2 UDB for iSeries added limited support for SQL Instead Of Triggers. IBM created Instead Of Triggers to enhance the behavior of Insert, Update, and Delete operations against SQL views. Views are commonly used to separate the logical data access from the physical definition of the table. Unfortunately the desired transparency often falls short in the case of Update, Delete, and Insert operations, since all but the simplest views are not updateable. For example, just the presence of a scalar function such as DayName or Decrypt in the Select list of a view definition makes that view read-only.

Consult the iSeries SQL Reference, for a complete description of the attributes that cause an SQL view to be not updateable.

Instead of Triggers extend the usability of non-updateable SQL views. When an Insert, Update, or Delete is performed against a read-only SQL view, an Instead of Triggers allows DB2 to call the trigger logic instead of signaling a read-only view error condition. The biggest difference with Instead Of triggers (compared to the existing DB2 trigger support) is that Instead Of triggers can only be defined over SQL views. The examples below include a view, my_logins, that is not updateable because of the Decrypt_Char function in the Select list. ince the view is automatically decrypting the password data when the data is read, the insert_my_logins trigger is defined to automatically decrypt data as Inserts and Updates are performed against the my_logins view.

Please read all the restrictions below before utilizing this limited V5R3 trigger support. Most of the restrictions will be addressed in a future iSeries release.

PTF requirements:

To enable the INSTEAD OF trigger support, the following PTFs must be applied to the system:

V5R3 Database Group PTF - Level 4 or greater
SI17399
SI17434

Syntax:

The CREATE TRIGGER syntax has only one clause that changes for INSTEAD OF triggers. The following syntax diagram segment shows this change.



A view-name must be specified instead of a table-name as the object the trigger is to be created on.

The following may not be specified on the CREATE TRIGGER statement when creating an INSTEAD OF trigger:

OF column-name clause for an UPDATE trigger
FOR EACH STATEMENT clause
WHEN clause

Authorization:

The privileges held by the authorization ID of the statement must include the following:

The ALTER privilege on the table or view on which the trigger is defined,
The SELECT privilege on the table or view on which the trigger is defined,
The WITH GRANT OPTION privilege on the view on which the INSTEAD OF trigger is defined.

For INSTEAD OF triggers the privileges must also include:

For each table and view referenced directly through the view's fullselect, or indirectly through views referenced in the fullselect :
The SELECT privilege on the table or view, and
The system authority *EXECUTE on the library containing the table or view.

Notes:

The SYSTRIGGERS catalog view will have a value of INSTEAD in the ACTION_TIMING column for an INSTEAD OF trigger.

View privileges when adding an INSTEAD OF trigger:

When the INSTEAD OF trigger is created, no additional DELETE, UPDATE or INSERT privileges for the specified operation of the trigger will be granted if the view was deletable, updatable, or insertable before the trigger was added When SQL names are specified, if the view was not deletable, updatable, or insertable before the trigger was added, the owner of the view, and the creator of the trigger will be granted the DELETE, UPDATE or INSERT privilege to the view for the specified operation of the trigger. No additional privileges will be granted for the *SYS naming option.

View privileges when removing an INSTEAD OF trigger:

When the INSTEAD OF trigger was created, no additional DELETE, UPDATE or INSERT privileges for the specified operation of the trigger were granted if the view was deletable, updatable, or insertable before the trigger was added. When the INSTEAD OF trigger is removed, if the view was not deletable, updatable, or insertable before the trigger was added, the privilege for the specified operation of the trigger will be revoked from the view for all users.

Examples:


CREATE VIEW my_logins(system, login, passwd) AS
SELECT system,login,decrypt_char(passwd) FROM regusers WHERE userid=USER

CREATE TRIGGER insert_my_logins INSTEAD OF INSERT ON my_logins
REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
INSERT INTO regusers VALUES (USER,n.system,n.login,ENCRYPT(n.passwd))

CREATE TRIGGER update_my_logins INSTEAD OF UPDATE ON my_logins
REFERENCING OLD AS o NEW AS n FOR EACH ROW MODE DB2SQL
UPDATE reguser SET system=n.system, login=n.login,
passwd=ENCRYPT(n.passwd) WHERE system=o.system AND login=o.login AND userid=USER

Restrictions and limitations:

An INSTEAD OF trigger must be defined for an SQL view. The view cannot be distributed across multiple systems and cannot be a system or catalog view.


For any given view, only one INSTEAD OF trigger may be defined for each operation (INSERT, UPDATE, and DELETE). Therefore, a view will have a maximum of three triggers.


The target view of an INSTEAD OF trigger must be based on one table. It cannot be based on another view, a derived table, or multiple tables.


A view can be created over a view for which an INSTEAD OF trigger is defined, but the INSTEAD OF trigger will not take effect for the dependent view. The I/O operation associated with the trigger will fail for the dependent view.


An ALTER TABLE statement is not allowed for the based-on table of a view for which an INSTEAD OF trigger is defined.


An INSTEAD OF trigger cannot be created on a view for which WITH CHECK OPTION was specified.


A view that specifies WITH CHECK OPTION cannot be created, directly or indirectly, over a view for which an INSTEAD OF trigger is defined.


An INSTEAD OF trigger cannot be created on a view that has a direct or indirect dependent view for which WITH CHECK OPTION is specified.


INSTEAD OF triggers cannot be saved to releases prior to V5R3M0.


INSTEAD OF triggers cannot be restored to a V5R3M0 system that does not have PTF SI16101 applied.


SQL programs and procedures that create INSTEAD OF triggers cannot be saved and restored to releases prior to V5R3M0.


The Display File Description (DSPFD) command and Retrieve File Description (QDBRTVFD) API do not support INSTEAD OF triggers. To get information about any INSTEAD OF triggers defined for a view, query the SYSTRIGGERS catalog view in QSYS2.


Column update authorities may not be granted to, or revoked from, any read-only columns in a view that become updatable columns when an INSTEAD OF UPDATE trigger is defined on the view.