ALTER TRIGGER (basic)

Invocation for ALTER TRIGGER (basic)

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES RUN behavior is in effect. For more information, see Authorization IDs and dynamic SQL.

Authorization for ALTER TRIGGER (basic)

The privilege set that is defined below must include at least one of the following:

• Ownership of the trigger
• The ALTERIN privilege on the schema
• SYSADM authority
• SYSCTRL authority
• System DBADM
• Installation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)

The authorization ID that matches the schema name implicitly has the ALTERIN privilege on the schema.

Privilege set: If the statement is embedded in an application program, the privilege set is the set of privileges that are held by the owner of the plan or package. If the statement is dynamically prepared, the privilege set is the set of privileges that are held by the SQL authorization ID of the process. The specified trigger name can include a schema name (a qualifier). However, if the schema name is not the same as one of the authorization ID of the process, one of the following conditions must be met:

• The privilege set includes SYSADM authority, SYSCTRL authority, or system DBADM authority.
• The authorization ID of the process has the ALTERIN privilege on the schema.

Description for ALTER TRIGGER (basic)

trigger-name

Identifies the trigger that is to be changed. The name, including the implicit or explicit schema name, must exist at the current server. The name must identify a basic trigger.

The trigger must not be obfuscated.

SECURED or NOT SECURED

Specifies that the trigger is to be changed to be secure or not secure. Changing a trigger between SECURED and NOT SECURED causes an implicit rebind of the trigger package. If an error is encountered during the implicit rebind of the trigger package, the ALTER TRIGGER statement returns the error.

SECURED

Specifies the trigger is considered secure.

SECURED must be specified for a trigger if its 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 the trigger is considered not secure.

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.

Notes for ALTER TRIGGER (basic)

Changing an existing trigger into an advanced trigger:

To change a basic trigger or a trigger defined prior to Db2 12 with new function activated into an advanced trigger, use one of the following methods:

• issue a DROP TRIGGER statement followed by a CREATE TRIGGER (advanced) statement, specifying the desired definition of the advanced trigger to redefine it. The existing trigger is effectively dropped, and a new advanced trigger is defined. This affects the trigger activation order for the table on which the trigger is defined.
• issue a CREATE TRIGGER (advanced) statement with the OR REPLACE clause, and specify the desired definition of the advanced trigger. The existing trigger is effectively dropped, and a new advanced trigger is defined. This affects the trigger activation order for the table on which the trigger is defined.

Changes are immediate:

Any changes that the ALTER TRIGGER statement causes to the definition of a trigger take effect immediately. The changed definition is used the next time that the trigger is activated.

Altering a trigger from NOT SECURED to SECURED:

Typically, the security administrator will examine the data that is accessed by a trigger, ensure that it is secure, and grant the CREATE_SECURE_OBJECT privilege to the owner of the trigger. After the trigger is changed to SECURED, the security administrator will revoke the CREATE_SECURE_OBJECT privilege from the owner of the trigger.

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, the user with SECADM authority needs to ensure that those functions are allowed to access that data and that an audit procedure is in place for all versions of those functions, and that all subsequent ALTER FUNCTION statements or changes to external packages are being reviewed by this audit process.

A trigger must be secure if its 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.

Altering a trigger from SECURED to NOT SECURED:

The ALTER TRIGGER statement returns an error if the subject table of the trigger is using row access control or column access control, or if the trigger is for a view and one or more of the underlying tables in the view definition is using row access control or column access control.

Altering obfuscated triggers:

Obfuscated triggers cannot be altered in any way.

Row access control and column access control that is not enforced for transition variables and transition tables:

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