Triggers (PDM)

A trigger is a segment of SQL code associated with a table or a view, which is invoked automatically whenever there is an attempt to modify data in the associated table or view with an insert, delete, or update command. A DBMS trigger is not associated with any table or view, and fires on modifications to the database structure itself, such as the creation or dropping of a table or events like startup, shutdown, login etc. You can use triggers to enforce referential integrity (where declarative constraints are not sufficient) and to implement sequences for columns.

In the PowerDesigner interface, table and view triggers are called simply triggers, while DDL or database triggers are called DBMS triggers. View and DBMS triggers are not supported by all DBMSs.

PowerDesigner provides trigger templates to generate triggers (see Trigger Templates) and template items, which are reusable blocks of SQL script that can be inserted into triggers or trigger templates (see Trigger Template Items) and you can modify these templates and items and create your own.

• Creating a Table or View Trigger
  You can create a trigger for a table from its property sheet and base it on a PowerDesigner template, or on a template of your own, or write it from scratch.
• Creating Triggers from References
  You can create triggers to enforce referential integrity individually or instruct PowerDesigner to create them by default.
• Creating a DBMS Trigger
  DBMS triggers are not associated with any table or view. You create them directly under the model.
• Trigger and DBMS Trigger Properties
  To view or edit a trigger's properties, double-click its Browser or list entry. The property sheet tabs and fields listed here are those available by default, before any customization of the interface by you or an administrator.
• Trigger Naming Conventions
  The pre-defined trigger templates that ship with PowerDesigner indicate naming conventions for the trigger scripts that it generates. The naming convention consists of a prefix indicating the trigger type followed by the table code.
• Calling a Related Procedure in a Trigger Template
  Some target databases do not accept code within a trigger statement. For these databases, a trigger template can call a related procedure as a parameter, which is defined in a procedure template. In these cases, procedure templates are listed in the list of trigger templates.
• Indicating Trigger Order for Multiple Triggers
  Some DBMSs allow you to have multiple triggers for the same insert, update, or delete event at the same time. You can indicate the order in which each trigger within the group fires.
• Defining Triggers with Multiple Events
  Some DBMSs support multiple events on triggers. If such is the case, the Ellipsis button to the right of the Event box on the trigger definition tab is available.
• Rebuilding Triggers
  PowerDesigner can rebuild triggers to ensure that they are attached to all tables joined by references to ensure referential integrity. You can instruct PowerDesigner to automatically rebuild triggers whenever a relevant change is made and you can manually rebuild triggers at any time.
• Trigger Templates
  PowerDesigner trigger templates allow you to write trigger code in a modular reusable fashion. We provide basic templates for before, after, and with insert, update, and delete events and for other types of triggers where supported by the DBMS. You can modify the code specified in these templates or create your own templates in the DBMS definition file or in your model.
• Trigger Template Items
  Trigger template items are named reusable blocks of script that can be inserted into triggers or trigger templates. In a generated trigger script, a template item calls a macro that implements a trigger referential integrity constraint or does any other updating work on tables in the database.
• Creating SQL/XML Queries with the Wizard
  You can use the SQL/XML Wizard to insert a SQL/XML query in the definition of a trigger, stored procedure, or function to store or retrieve data, in an XML format, from relational databases supporting SQL/XML. The wizard, allows you to select tables and views from a PDM to build a mapped XML model. This XML model (which does not appear in the workspace) is used to generate SQL/XML queries from global elements.
• Generating Triggers and Procedures
  You can create or modify database triggers to a script or to a live database connection.