Versions Compared

Version Old Version 6 New Version 7
Changes made by

Documentation Reviewer

Documentation Reviewer

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Enabling a Trigger in Microsoft SQL Server Management Studio

By enabling triggers that track changes are turned on or off for these tables. Most of these tables have a delete trigger with a name in the format of  ‘XXXX_D_CDC’ where XXXX is the table name and an insert/update trigger in the form of ‘XXXX_IU_CDC’. Two tables make an exception to this rule and have 3 triggers, one for Insert, one for update and one for delete. They are tables SV00300 and SV00301.

To enable a trigger in Microsoft SQL Server Management Studio:

  1. Launch Microsoft SQL Server Management Studio.
  2. Expand Databases > your company database > Tables.
  3. Expand the appropriate table.
  4. Expand Triggers.
  5. Right-click on the XXXX_D_CDC trigger and select Enable. (This is the "delete" trigger.)
  6. Right-click on the XXXX_IU_CDC trigger and select Enable. (This is the "insert/update" trigger.)


Enabled Triggers Script

This script provides a list of all enabled scripts for a given company:

SELECT sysobjects.name AS trigger_name, OBJECT_NAME(parent_obj) AS table_name,

             OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]

FROM sysobjects

WHERE sysobjects.type = 'TR' AND sysobjects.name LIKE '%_CDC'

       and OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 0

ORDER BY disabled,sysobjects.name ASC

Enabled Triggers Query Results

...

If you need to enable triggers for a table not found in this list, run the following script to find the triggers for a given table not in the list and then go through Microsoft SQL Server Management Studio and manually enable the trigger:

SELECT sysobjects.name AS trigger_name, OBJECT_NAME(parent_obj) AS table_name,

             OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') AS [disabled]

FROM sysobjects

WHERE sysobjects.type = 'TR' AND (sysobjects.name LIKE '%_D_CDC' or sysobjects.name LIKE '%_IU_CDC')

       and OBJECTPROPERTY(id, 'ExecIsTriggerDisabled') = 1

ORDER BY disabled,sysobjects.name ASC

Disabled Triggers Query Results

...