ALTER TRIGGER

The ALTER TRIGGER statement changes the description of the trigger at the current server.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

Authorization

If ENABLE or DISABLE is specified, the privileges held by the authorization ID of the statement must include at least one of the following:

For the trigger identified in the statement:
- The system authority *USE on the Change Physical File Trigger (CHGPFTRG) command,
- The ALTER privilege on the table or view on which the trigger is defined, and
- The system authority *EXECUTE on the library containing the trigger.
Database administrator authority

If the SECURED option is specified, or if the NOT SECURED option is specified and the trigger is currently secured:

The authorization ID of the statement must have security administrator authority. See Administrative authority.

For information about the system authorities corresponding to SQL privileges, see Corresponding System Authorities When Checking Privileges to a Table or View.

Syntax

Notes:

¹ The same clause must not be specified more than one time.

Description

trigger-name

Identifies the trigger to be altered. The trigger-name must identify a trigger that exists at the current server. It can be either an SQL or a native trigger.

SECURED or NOT SECURED

Specifies whether the trigger is considered secure for row and column access control. If row or column access control is active for the triggering table, altering the trigger from SECURED to NOT SECURED will return an error.

SECURED: Specifies that the trigger is considered secure for row access control and column access control.; SECURED must be specified for a trigger whose subject table is using row access control or column access control. SECURED must also be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.
NOT SECURED: Specifies that the trigger is considered not secure for row access control and column access control.; NOT SECURED must not be specified for a trigger whose subject table is using row access control or column access control. NOT SECURED must also not be specified for a trigger that is created for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

ENABLE or DISABLE

Specifies the state to which the trigger is to be changed.

ENABLE: The trigger will be called during the appropriate data change operations.
DISABLE: The trigger will not be called during the appropriate data change operations.

Notes

Altering a trigger from NOT SECURED to SECURED: The trigger is considered secure after the ALTER TRIGGER statement is executed. DB2® treats the SECURED attribute as an assertion that declares that the user has established an audit procedure for all activities in the trigger body. If a secure trigger references user-defined functions, DB2 assumes those functions are secure without validation. If those functions can access sensitive data, a user with security administrator authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for those functions, and that all subsequent ALTER FUNCTION statements are being reviewed through this audit process.

Transition variable values and row and column access control: Row and column access control is not enforced for transition variables and transition tables. If row or column access control is enforced for the triggering table, row permissions and column masks are not applied to the initial values of transition variables and transition tables. Row and column access control enforced for the triggering table is also ignored for transition variables and transition tables that are referenced in the trigger body or are passed as arguments to user-defined functions invoked within the trigger body. To ensure there are no security concerns for SQL statements accessing sensitive data in transition variables and transition tables, the trigger must be created with the SECURED option. If the trigger is not secure, row access control and column access control cannot be enforced for the triggering table.

Example

Example 1: Change the definition of trigger TRIGGER1 to secured:

  ALTER TRIGGER TRIGGER1 
    SECURED

Example 2: Change the definition of trigger TRIGGER1 to not secured:

  ALTER TRIGGER TRIGGER1 
    NOT SECURED