Creating and Editing Triggers
Go Up to Creating and Editing SQL Procedures
Contents
Triggers are code objects associated with a table that automatically execute when the table is modified by an SQL INSERT, UPDATE, or DELETE statement. Triggers can contain complex SQL statements, such as those required to query other tables, but cannot accept parameters or arguments, and cannot perform commit or rollback operations. They are also subject to coding errors like every other script or application.
Because triggers are customizable and execute automatically, they are often used to:
- Enforce referential integrity rules. For example, when a new customer in the eastern territory is added to the customer table, the new customer information is also added to the table for the sales representative handling that region.
- Enforce business rules on table data. For example, restricting an order insertion based on the customer's account status. If the customer's payment is overdue or the order exceeds the customer's credit limit, the order is rejected.
- Synchronize information in another table when a table is updated. For example, you can create a delete trigger on the product table that the trigger uses to locate and delete matching rows in the sales table and pending orders table.
- Initiate an action external to the SQL Server when an update occurs. For example, when a new employee is hired and added into the employees table, an e-mail is sent to the human resources department that prompts them to perform certain actions like arranging desk space and computer equipment, and ensuring all that all required paperwork is sent to the new employee and is returned completed to the human resources department.
- Prevent some types of operations, such as UPDATEs. For example, a trigger can roll back updates that attempt to apply a bonus (stored in the renumeration table) to employees (stored in the persons table) who work less than 16 hours a week.
You can view the triggers associated with your models from the Triggers node in the Data Model Explorer, but to create or edit triggers, you must use the Triggers tab of the table with which you want to associate the trigger.
The generated trigger code is customized for each target database platform. ER/Studio Data Architect uses templates to implement trigger code generation. SQL Procedures can be triggers that are a special type of procedure.
ER/Studio Data Architect supports the following trigger types:
- Referential Integrity Triggers ensure implied relationships in the database are enforced, by preventing users from creating discrepancies in the database. They inform the database how to manage or process the procedural SQL commands, which enforce the business rules of an organization. ER/Studio Data Architect defines Referential Integrity Triggers by establishing insert, update and delete options between parent and child tables.
On the Trigger tab of the Relationship Editor, you can define the level of referential integrity and SQL insert, update, and delete behaviors. For more information, see Create a Referential Integrity Trigger.
- User-Defined Triggers are usually customized for special situations to override the default capabilities of database generated code. User-defined triggers are closely tied to DBMS-specific code, and therefore, are implemented only in physical models. There are three types of user-defined triggers:
- Scripted Triggers that are written in the raw procedural code of the database such as Oracle's PL/SQL or Microsoft's Transact-SQL. You can reverse-engineer these objects if they exist on the database. You can also display them in the Data Model Window. Reverse-engineered triggers have table names and other object references hard-coded into their underlying code. For more information, see Create a Scripted User-Defined Trigger.
- Templated Triggers that do not have names of specific objects the trigger references, such as table names and specific columns hard-coded into their underlying code. This enables these triggers to be reused across various objects of an application, and the body of the trigger code to be reused across models. For more information, see Create a Templated User-Defined Trigger.
- Reusable Triggers that can perform actions against any number of tables. For more information, see Create a Reusable User-Defined Trigger.
Trigger Example
Sub Main
Dim trigName As String
Dim trigBody As String
Dim attrib As AttributeObj
Dim crlf As String
crlf = Chr(13) + Chr(10)
trigName = Left(CurrEntity.TableName, 24) + "UpdTrg"
trigBody = "CREATE OR REPLACE TRIGGER " + trigName + " AFTER UPDATE ON " + CurrEntity.TableName + crlf
trigBody = trigBody + "REFERENCING OLD AS OLD NEW AS NEW" + crlf
trigBody = trigBody + "FOR EACH ROW" + crlf
trigBody = trigBody + "BEGIN" + crlf
trigBody = trigBody + "UPDATE " + CurrEntity.TableName + " SET UPDATEDATE = SYSDATE WHERE "
For Each attrib In CurrEntity.Attributes
If attrib.PrimaryKey = True Then
If Right(trigBody,6) <> "WHERE " Then
trigBody = trigBody + " AND "
End If
trigBody = trigBody + attrib.ColumnName + " =:OLD." + attrib.ColumnName
End If
Next attrib
trigBody = trigBody + ";" + crlf + "END;"
'Resultstring outputs the trigger to the DDL script when the Generate Database
'wizard is used. The string variable used to generate the trigger DDL needs to be set to it.
resultstring = trigBody
'This message box is used to view the SQL when debugging the VB code. A table has to be selected.
MsgBox(trigBody)
End Sub
The following database platforms support Triggers:
- IBM DB2 for LUW
- IBM DB2 for OS/390
- IBM DB2 for z/OS
- InterBase
- Microsoft SQL Server
- Oracle
- Sybase ASA
- Sybase Adaptive Server IQ
- Sybase SQL Server System ASE
Create a Referential Integrity Trigger
Referential integrity triggers, or system triggers, ensure implied relationships in the database are enforced, by preventing users from creating discrepancies in the database. They inform the database how to manage or process the templated procedures, which enforce the business rules of an organization. On the Trigger tab of the Relationship Editor, you can define referential integrity triggers by establishing insert, update and delete behaviors for the parent and child tables.
Note: You cannot create a referential integrity trigger for a non-specific relationship.
- On the Data Model Window, create a relationship between two tables you want to ensure get updated when an SQL INSERT, UPDATE or DELETE operation is performed on one of the tables.
- On the Data Model Window, double-click the relationship.
- On the Relationship Editor, click the Trigger tab.
The following describes the options that require additional explanation.
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the trigger must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody - ER/Studio Data Architect uses templates to implement referential integrity trigger code generation. The generated trigger code is customized for each target database platform. For each INSERT, UPDATE or DELETE action, you can apply the following trigger templates based on type, selections, and level of data modification required:
- Parent Actions: Defines what the child does when the parent is updated.
- Insert: None: This cannot be changed.
- None (No Action): The trigger verifies the existence of the foreign key values in the parent table's primary key. If the values cannot be validated, the update or delete fails the referential integrity checks.
- Set Null: The trigger verifies the existence of the foreign key values in the parent table's primary key. If the values cannot be validated, then the trigger sets the foreign key value to null in the child table and then updates or deletes the parent. For example, you have a parent table persons and a child table tasks, which through a mandatory relationship share an id. By using a Set Null trigger you can allow the user to insert data into the tasks table that contains an id for which their is no corresponding value in the persons table. The database will not throw a referential integrity error in this case.
- Set Default: Sets the foreign key to its DEFAULT value, then updates or deletes the parent.
- Restrict: The trigger verifies the existence of foreign key values in the parent table's primary key. If the values cannot be validated, then the trigger prevents the insertion, update or deletion of data. For example, you have a parent table persons and a child table tasks, which through a mandatory relationship share an id. By using a Restrict trigger you can prevent the user from inserting data into the tasks table that contains an id for which their is no corresponding value in the persons table. The database will throw a referential integrity error in this case.
- Cascade: If a primary key is updated or deleted, the trigger cascades that modification to corresponding foreign key values in dependent tables.
Create a Scripted User-Defined Trigger
- On the Data Model Window or the Data Model Explorer, select the table on which you want to create a Trigger.
- Choose Edit > Edit Database Table .
- Tip: Alternatively, on the Data Model Window, open the Table Editor by double-clicking the table or right-clicking the object and then selecting Edit Table.
- On the Table Editor, click the Dependencies tab.
- Click the Add button and then select Triggers > Scripted.
- Complete the Trigger Editor and then click Finish to create the trigger.
The following helps you complete the Trigger Editor options:
Notes
- When generating a database using the DDL Generation Wizard, you can choose which triggers to include and whether they should be generated using either CREATE or DROP statements. On the General Options tab, you can choose to create system triggers such as referential integrity triggers.
- Triggers will display in physical model of the Data Model Explorer in this format:
triggerName. assocatedTableName. - You can display the trigger actions by selecting Trigger Actions on the Relationship tab of the Diagram and Display Options dialog. For example, when trigger actions are displayed U:R displays next to an Employee table with an Update Restrict trigger.
- There are certain commands, statements, and operations that cannot be used in trigger programs. To avoid generating exceptions with your triggers, consult your DBMS documentation.
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the trigger must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody - The Trigger Editor includes find and replace functionality for working with SQL text strings.
- Name: Lets you enter a trigger name or edit the existing one.
SQL tab
Lets you enter a CREATE TRIGGER statement for the trigger, or edit the existing script. If you do not want to enter an SQL script, you can use the Import button to import it.
- Export: Lets you export the SQL script to a *.sql file. This is useful for when you want to create additional triggers based on the one you are currently creating. You can export the code, and later import it into another procedure. When you click Export, the Save As dialog box opens. Enter the file name. ER/Studio Data Architect saves the files in the Model folder, if you want to save your files in a different folder, browse and locate it.
- Import: Lets you import *.sql files. When you click Import, the Open dialog box opens. You can enter the name of the *.sql file, or browse and locate it.
- Validate: Lets you validate the SQL script. If ER/Studio Data Architect detects any errors, it returns them in a message box. Errors include the error type, line, and column information.
Description tab
Includes a text box where you can enter a Trigger description.
Attachment Bindings tab
Bind an external piece of information, or attachment to the trigger. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
Create a Templated User-Defined Trigger
- On the Data Model Window or the Data Model Explorer, select the table on which you want to create a Trigger.
- Choose Edit > Edit Database Table .
- Tip: Alternatively, on the Data Model Window, open the Table Editor by double-clicking the table or right-clicking the object and then selecting Edit Table.
- On the Table Editor, click the Dependencies tab.
- Click the Add button and then select Triggers > Templated.
- Complete the Trigger Editor and then click Finish to create the trigger.
Tip: Once you have created the auxiliary table, you can edit it by right-clicking the auxiliary table you want to change, and then selecting Edit Trigger.
Description page/tab
Enter or edit a definition for the description. If the target database supports it, ER/Studio Data Architect adds this definition as a comment when generating SQL code.
Attachment Bindings tab
Bind an external piece of information, or attachment to the trigger. You can also remove an attachment from an object, override an attachment binding's default value, or change the position of a bound attachment. To override the value of the attachment you have moved to the Selected Attachments grid, double-click the Value field of the target attachment. ER/Studio Data Architect opens the Value Override Editor or a list depending on the attachment datatype. Attachments are created in the Attachments folder of the Data Dictionary. For more information, see Attaching External Documents to the Data Model.
Create a Reusable User-Defined Trigger
Reusable user-defined triggers are created in the Data Dictionary. For more information, see Create and Edit Reusable Triggers
Notes
- The Trigger Editor includes find and replace functionality for working with SQL text strings.
- In order for code to be generated for procedural logic such as triggers and procedures, the results of the trigger must be stored in a variable called resultstring. For example, in the Northwind.dm1 sample model, the Data Dictionary lists a reusable trigger, SYSUPDATE, for the Oracle platform. The code for this trigger includes the following statement, which is required for code to be generated for the trigger:
resultstring = trigBody