Class DB2Trigger {Analysis} derived from: TriggerDB2AccessPlan

Documentation
SQL Reference for Cross-Platform Development - v1.1
http://www7b.software.ibm.com/dmdd/library/techarticle/0206sqlref/0206sqlref.html

Triggers (Chapter 1. Concepts 7)

A trigger defines a set of actions that are executed automatically whenever a delete, insert, or update operation occurs on a specified base table. When such an SQL operation is executed, the trigger is said to be activated. Triggers can be used along with referential constraints and check constraints to enforce data integrity rules. Triggers are more powerful than constraints because they can also be used to cause updates to other tables, automatically generate or transform values for inserted or updated rows, or invoke functions that perform operations both inside and outside of DB2. For example, instead of preventing an update to a column if the new value exceeds a certain amount, a trigger can substitute a valid value and send a notice to an administrator about the invalid update.

Triggers are a useful mechanism to define and enforce transitional business rules that involve different states of the data (for example, salary cannot be increased by more than 10 percent). Such a limit requires comparing the value of a salary before and after an increase. For rules that do not involve more than one state of the data, consider using referential and check constraints. Triggers also move the application logic that is required to enforce business rules into the database, which can result in faster application development and easier maintenance because the business rule is no longer repeated in several applications, but one version is centralized to the trigger. With the logic in the database, for example, the previously mentioned limit on increases to the salary column of a table, DB2 checks the validity of the changes that any application makes to the salary column. In addition, the application programs do not need to be changed when the logic changes.

There are a number of criteria that are defined when creating a trigger which are used to determine when a trigger should be activated.
- The subject table defines the base table for which the trigger is defined.
- The trigger event defines a specific SQL operation that modifies the subject table. The operation could be delete, insert, or update.
- The trigger activation time defines whether the trigger should be activated before or after the trigger event is performed on the subject table. The statement that causes a trigger to be activated will include a set of affected rows. These are the rows of the subject table that are being deleted, inserted or updated. The trigger granularity defines whether the actions of the trigger will be performed once for the statement or once for each of the rows in the set of affected rows. The trigger action consists of an optional search condition and a set of SQL statements that are executed whenever the trigger is activated. The SQL statements are only executed if no search condition is specified or the specified search condition evaluates to true.

The triggered action may refer to the values in the set of affected rows. This is supported through the use of transition variables. Transition variables use the names of the columns in the subject table qualified by a specified name that identifies whether the reference is to the old value (prior to the update) or the new value (after the update). The new value can also be changed using the SET transition-variable statement in before update or insert triggers. Another means of referring to the values in the set of affected rows is using transition tables.

Transition tables also use the names of the columns of the subject table but have a name specified that allows the complete set of affected rows to be treated as a table. Transition tables can only be used in after triggers. Separate transition tables can be defined for old and new values. Multiple triggers can be specified for a combination of table, event, or activation time. The order in which the triggers are activated is the same as the order in which they were created. Thus, the most recently created trigger will be the last trigger activated. The activation of a trigger may cause trigger cascading. This is the result of the activation of one trigger that executes SQL statements that cause the activation of other triggers or even the same trigger again. The triggered actions may also cause updates as a result of the original modification, which may result in the activation of additional triggers. With trigger cascading, a significant chain of triggers may be activated causing significant change to the database as a result of a single delete, insert or update statement.

The actions performed in the trigger are considered to be part of the operation that caused the trigger to be executed.
- The database manager ensures that the operation and the triggers executed as a result of that operation either all complete or are backed out. Operations that occurred prior to the triggering operation are not affected.
- The database manager effectively checks all constraints (except for a constraint with a RESTRICT delete rule) after the operation and the associated triggers have been executed.

Triggers (Appendix E. SQLSTATE values -- common return codes)

SQLSTATE 51037 - The operation is not allowed because a trigger has been marked inoperative.


Parent PackageDB2ModelAbstractNo
Export ControlPublicAccessLink Class forNone
Class KindNormalClassCardinalityn
Space ConcurrencySequential
PersistenceNo  


Operations
NameSignatureClass
addEAnnotationEAnnotation addEAnnotation (String source)SQLObject
addEAnnotationDetailvoid addEAnnotationDetail (EAnnotation eAnnotation, String key, String value)SQLObject
getEAnnotationDetailString getEAnnotationDetail (EAnnotation eAnnotation, String key)SQLObject
setAnnotationDetailvoid setAnnotationDetail (EAnnotation eAnnotation, String key, String value)SQLObject
removeEAnnotationDetailvoid removeEAnnotationDetail (EAnnotation eAnnotation, String key)SQLObject
getEAnnotationEAnnotation getEAnnotation (String source)SQLObject
getEAnnotationEAnnotation getEAnnotation (String source)EModelElement
eClassEClass eClass ()EObject
eIsProxyboolean eIsProxy ()EObject
eResourceEResource eResource ()EObject
eContainerEObject eContainer ()EObject
eContainingFeatureEStructuralFeature eContainingFeature ()EObject
eContainmentFeatureEReference eContainmentFeature ()EObject
eContentsEEList eContents ()EObject
eAllContentsETreeIterator eAllContents ()EObject
eCrossReferencesEEList eCrossReferences ()EObject
eGetEJavaObject eGet (EStructuralFeature feature)EObject
eGetEJavaObject eGet (EStructuralFeature feature, boolean resolve)EObject
eSet eSet (EStructuralFeature feature, EJavaObject newValue)EObject
eIsSetboolean eIsSet (EStructuralFeature feature)EObject
eUnset eUnset (EStructuralFeature feature)EObject


Attributes
NameClassTypeInitial Value
operativeDB2Triggerbooleantrue
actionGranularityTriggerActionGranularityTypeSTATEMENT
whenTriggerSearchCondition 
timeStampTriggerDate 
actionTimeTriggerActionTimeType 
updateTypeTriggerboolean 
insertTypeTriggerboolean 
deleteTypeTriggerboolean 
oldRowTriggerString 
newRowTriggerString 
oldTableTriggerString 
newTableTriggerString 
descriptionSQLObjectString 
labelSQLObjectString 
nameENamedElementString 


Associations
NameMy RoleMy ClassOther RoleOther Element
--Not Named----Not Named--TriggeractionStatementSQLStatement
--Not Named--triggersTriggerschemaSchema
--Not Named----Not Named--TriggertriggerColumnColumn
--Not Named--triggersTriggersubjectTableTable
--Not Named----Not Named--SQLObjectcommentsComment
=--Not Named--SQLObjectdependenciesDependency
--Not Named--objectSQLObjectprivilegesPrivilege
--Not Named--actionObjectsSQLObject--Not Named--Privilege
--Not Named--eModelElementEModelElementeAnnotationsEAnnotation
--Not Named--contentsEObject--Not Named--EAnnotation
--Not Named--referencesEObject--Not Named--EAnnotation
--Not Named--targetEndEObject--Not Named--Dependency
--Not Named--accessPlansDB2AccessPlan--Not Named--DB2Schema


Generalization Relationships
NameClassSupplier
--Not Named--DB2TriggerTrigger
--Not Named--DB2TriggerDB2AccessPlan
--Not Named--TriggerSQLObject
--Not Named--SQLObjectENamedElement
--Not Named--ENamedElementEModelElement
--Not Named--EModelElementEObject
--Not Named--DB2AccessPlanSQLObject



Property Settings

Data Modeler
dmItemFalseDMName 
IsTableFalseIsViewFalse
IsDomainFalseIsSPPackageFalse
Synonymns TableSpaceID 
SourceId SourceType 
CorrelationName SelectClause 
IsUpdateableTrueCheckOptionNone
IsSnapShotFalseIsDistinctFalse
PersistToServer IsPackageFalse