Defining Triggers for a Database
A trigger is processing recorded in a database, which automatically triggers on updating a table.
Creating Triggers
Triggers are defined at the level of database tables.
It should be noted that triggers are defined as a function of the target DBMS; this is why it is important to check that the target DBMS is correct before creating triggers.If the target DBMS is later modified, triggers created for the DBMS are not deleted but deactivated.
To create a trigger in HOPEX Data Governance:
1. On the desktop, click the navigation menu then Architecture > Hierarchy View.
In HOPEX Information Architecture, click the navigation menu then Data Architecture > Logical Data Assets.2. Expand the folder of the database then the table concerned.
3. Right-click the Trigger folder and select New > Trigger.
The dialog box for creating a trigger opens.
4. Specify the name of the trigger and actions triggered. See Trigger triggering.
Trigger triggering
Triggering can occur following one of the three following actions:
• At Creation of a row in the table.
• At Deletion of a row.
• At Modification of the table or of a particular column.
In addition, you can choose to run it Before or After these actions, on the entire table, or on each row concerned.
References
The "Reference of old rows" and "Reference of new rows" fields create in the trigger code references to lines inserted, deleted or updated.
The name indicated in the "Reference of old rows" field corresponds to the line that existed before the update.
The name in the "Reference of new rows" field indicates the line after the update.
In the case of insertion, only the new line is valid.
In the case of deletion, only the old line is valid.
SQL Definition
The SQL Definition option in the properties window of the trigger presents the trigger code.
To display the trigger code:
1. Right-click the trigger and select Properties.
The properties window of the trigger appears.
2. Click the drop-down list then click Texts.
3. Select the SQL Definition tab.
Repository Integrity
Repository integrity is managed by creation of foreign keys on a database.
It groups all constraints allowing a check of the impact of modification of a table in tables connected to it.
It could be that the existence of keys in certain DBMSs does not involve a systematic check. It could also be that you wish to customize constraints to be applied on a particular table.
This is why you can generate in triggers the code that corresponds to repository integrity management.
To generate repository integrity of a table:
1. Right-click the database and select Trigger Initialization.
The trigger generation dialog box opens.
2. Select one of the options offered:
• Generate Trigger by type
• Generate Trigger by repository integrity
3. Select the tables of the database.
4. Click Next.
Triggers are automatically created for the selected tables.
When generation has been completed, the triggers appear under the Trigger folder available under each table. There are three trigger types:
• An update trigger (U_followed by table name), which enables specification of the action to be carried out in case of modification of a line of the table that is part of the foreign key.
• A delete trigger (D_), which specifies the action to be carried out in case of deletion.
• An insert trigger (I_), which specifies the action to be carried out in case of insertion.
These triggers are only valid for a target DBMS. When you change DBMS, you must regenerate the triggers.
See also Using Stored Procedures.