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>
|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.|
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.
ExampleThis 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.