ALTER TRIGGER statement (basic trigger)

Start of changeThe ALTER TRIGGER (basic) statement changes the description of a basic trigger at the current server.End of change

FL 500For a description of the differences between basic and advanced triggers, see Triggers.

Invocation for ALTER TRIGGER (basic)

Start of changeThis 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.End of change

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
  • Start of changeInstallation SYSOPR authority (when the current SQLID of the process is set to SYSINSTL)End of change

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.
If the SECURED option is specified, at least one of the following privileges is required:
  • SECADM authority
  • CREATE_SECURE_OBJECT privilege
Note: If the SEPARATE SECURITY subsystem parameter is set to NO, SYSADM authority has implicit SECADM authority.
Start of change

Syntax for ALTER TRIGGER (basic)

Read syntax diagramSkip visual syntax diagramALTER TRIGGERtrigger-nameoption-list

option-list:

Read syntax diagramSkip visual syntax diagram NOT SECUREDSECURED
End of change

Description for ALTER TRIGGER (basic)

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

Start of changeThe trigger must not be obfuscated.End of change

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)

Start of changeChanging an existing trigger into an advanced trigger:End of change
Start of changeFL 500To 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.
End of change
Start of changeChanges are immediate:End of change
Start of changeAny 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.End of change
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.
Start of changeAltering obfuscated triggers:End of change
Start of changeObfuscated triggers cannot be altered in any way.End of change
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.

Examples for ALTER TRIGGER (basic)

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

ALTER TRIGGER TRIGGER1
 SECURED;