Copy Triggers | Teradata Data Mover - 17.11 - About Copying Triggers - Teradata Data Mover

Teradata® Data Mover User Guide

Product
Teradata Data Mover
Release Number
17.11
Release Date
October 2021
Content Type
User Guide
Publication ID
B035-4101-091K
Language
English (United States)

When copying a trigger from a source database to a target database, specify the selection="included" attribute of the trigger tag when defining the trigger, as shown in the code example below.

<triggers>
  <trigger selection= "included">
	<database>west1000</database>
	<subject_table_database>west1000</subject_table_database>
	<table>employee</table>
	<name>RaiseTrig</name>
	<action_time enabled="NO">BEFORE</action_time> 
  </trigger>
</triggers>
Parameters for the Trigger Element
Parameter Values
selection
  • Specify "included" to copy the trigger.
  • Specify "unselected" so that the trigger is not copied.
database Database name in which the trigger resides.
subject_table_database Database name in which the table that is associated with the trigger resides.
table Name of the table that is associated with the trigger.
name Name of the trigger.
action_time
  • Specify "BEFORE" to place the trigger on the target table before the table is loaded. This results in a trigger action for each row that is copied from the source.
  • Specify "AFTER" to place the trigger on the target table after the table is loaded. This results in no trigger action for the rows that are being copied.
enabled
  • Specify "YES" to enable the trigger on the target.
  • Specify "NO" to disable the trigger on the target.

The value must be "YES" to enable the action_time parameter. If the value is "NO", the action_time parameter is invalid.

To verify if a trigger is enabled or disabled on the target, use the SELECT query from dbc.TriggersX and look at the EnabledFlag column.

Rules and Restrictions

  • Copying a trigger without copying its associated table results in an error.
  • If the object being copied does not exist on the target database, it is created on the target database. If the object does exist on the target database, the object is overwritten – unless the overwrite_existing_objects property is set to false. In that case, a create time error is generated.
  • When copying a table that already exists on the target with associated triggers, the target table is overwritten and the triggers are replaced on the newly copied table.
  • Attempting to rename or relocate a table associated with or referenced by a copied trigger results in an error.
  • If the trigger name in the trigger definition is not fully qualified, the trigger is created in the database having the same name as the source trigger database.
  • If the table names in the trigger definition are not fully qualified, those tables must exist in the database having the same name as the source trigger database to avoid an error.

Example

This example creates a trigger on the source machine without fully qualifying object names in the definition:
CREATE TRIGGER RaiseTrig
 AFTER INSERT ON Employee
 FOR EACH ROW
 ( INSERT INTO SalaryLog VALUES ('Hello','Hi',23, 43); );
In this case, copying the trigger to the target creates the trigger in the database having the same name as the source trigger database. If the Employee and SalaryLog tables do not exist there, an error results.