Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 7 Next »

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

These are the results of the enabled triggers script query, assuming all nine triggers are enabled:

Trigger Name

Table Name

Disabled

JC00102_D_CDC

JC00102

0

JC00102_IU_CDC

JC00102

0

JC00701_D_CDC

JC00701

0

JC00701_IU_CDC

JC00701

0

RM00101_D_CDC

RM00101

0

RM00101_IU_CDC

RM00101

0

SV00115_D_CDC

SV00115

0

SV00115_IU_CDC

SV00115

0

sv00196_D_CDC

sv00196

0

sv00196_IU_CDC

sv00196

0

SV00200_D_CDC

SV00200

0

SV00200_IU_CDC

SV00200

0

SV00300_D_CDC

SV00300

0

SV00300_I_CDC

SV00300

0

SV00300_U_CDC

SV00300

0

SV00301_D_CDC

SV00301

0

SV00301_I_CDC

SV00301

0

SV00301_U_CDC

SV00301

0

SV00400_D_CDC

SV00400

0

SV00400_IU_CDC

SV00400

0

Disabled Triggers Script

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

These are the query results from the disabled triggers script, assuming the default nine triggers have been enabled:

Trigger Name

Table Name

Disabled

GL00100_D_CDC

GL00100

1

GL00100_IU_CDC

GL00100

1

JC00107_D_CDC

JC00107

1

JC00107_IU_CDC

JC00107

1

JC00201_D_CDC

JC00201

1

JC00201_IU_CDC

JC00201

1

JC00401_D_CDC

JC00401

1

JC00401_IU_CDC

JC00401

1

JC00501_D_CDC

JC00501

1

JC00501_IU_CDC

JC00501

1

JC00601_D_CDC

JC00601

1

JC00601_IU_CDC

JC00601

1

JC00610_D_CDC

JC00610

1

JC00610_IU_CDC

JC00610

1

JC00801_D_CDC

JC00801

1

JC00801_IU_CDC

JC00801

1

JC01001_D_CDC

JC01001

1

JC01001_IU_CDC

JC01001

1

JC01002_D_CDC

JC01002

1

JC01002_IU_CDC

JC01002

1

JC01004_D_CDC

JC01004

1

JC01004_IU_CDC

JC01004

1

JC01501_D_CDC

JC01501

1

JC01501_IU_CDC

JC01501

1

JC01504_D_CDC

JC01504

1

JC01504_IU_CDC

JC01504

1

JC01701_D_CDC

JC01701

1

JC01701_IU_CDC

JC01701

1

JC01801_D_CDC

JC01801

1

JC01801_IU_CDC

JC01801

1

JC20705_D_CDC

JC20705

1

JC20705_IU_CDC

JC20705

1

JC40106_D_CDC

JC40106

1

JC40106_IU_CDC

JC40106

1

JC40201_D_CDC

JC40201

1

JC40201_IU_CDC

JC40201

1

JC40209_IU_CDC

JC40209

1

K2A_ResourceExtension_D_CDC

K2A_ResourceExtension

1

K2A_ResourceExtension_IU_CDC

K2A_ResourceExtension

1

POP10100_D_CDC

POP10100

1

POP10100_IU_CDC

POP10100

1

POP10110_D_CDC

POP10110

1

POP10110_IU_CDC

POP10110

1

RM00102_D_CDC

RM00102

1

RM00102_IU_CDC

RM00102

1

RM00301_D_CDC

RM00301

1

RM00301_IU_CDC

RM00301

1

SV_00415_D_CDC

SV_00415

1

SV_00415_IU_CDC

SV_00415

1

SV00045_D_CDC

SV00045

1

SV00045_IU_CDC

SV00045

1

SV00077_D_CDC

SV00077

1

SV00077_IU_CDC

SV00077

1

SV000805_D_CDC

SV000805

1

SV000805_IU_CDC

SV000805

1

SV00100_D_CDC

SV00100

1

SV00100_IU_CDC

SV00100

1

SV00113_D_CDC

SV00113

1

SV00113_IU_CDC

SV00113

1

SV00114_D_CDC

SV00114

1

SV00114_IU_CDC

SV00114

1

SV00155_D_CDC

SV00155

1

SV00155_IU_CDC

SV00155

1

SV00201_D_CDC

SV00201

1

SV00201_IU_CDC

SV00201

1

SV00310_D_CDC

SV00310

1

SV00310_IU_CDC

SV00310

1

SV00320_D_CDC

SV00320

1

SV00320_IU_CDC

SV00320

1

SV00325_D_CDC

SV00325

1

SV00325_IU_CDC

SV00325

1

sv00326_D_CDC

sv00326

1

sv00326_IU_CDC

sv00326

1

SV00330_D_CDC

SV00330

1

SV00330_IU_CDC

SV00330

1

SV00331_D_CDC

SV00331

1

SV00331_IU_CDC

SV00331

1

SV00403_D_CDC

SV00403

1

SV00403_IU_CDC

SV00403

1

SV00405_D_CDC

SV00405

1

SV00405_IU_CDC

SV00405

1

SV00410_D_CDC

SV00410

1

SV00410_IU_CDC

SV00410

1

SV00411_D_CDC

SV00411

1

SV00411_IU_CDC

SV00411

1

SV00420_D_CDC

SV00420

1

SV00420_IU_CDC

SV00420

1

sv00430_D_CDC

sv00430

1

sv00430_IU_CDC

sv00430

1

SV00500_D_CDC

SV00500

1

SV00500_IU_CDC

SV00500

1

SV00560_D_CDC

SV00560

1

SV00560_IU_CDC

SV00560

1

SV00572_D_CDC

SV00572

1

SV00572_IU_CDC

SV00572

1

SV00701_D_CDC

SV00701

1

SV00701_IU_CDC

SV00701

1

SV00735_IU_CDC

SV00735

1

SV01200_D_CDC

SV01200

1

SV01200_IU_CDC

SV01200

1

SV40180_D_CDC

SV40180

1

SV40180_IU_CDC

SV40180

1

SV40182_D_CDC

SV40182

1

SV40182_IU_CDC

SV40182

1

SV40184_D_CDC

SV40184

1

SV40184_IU_CDC

SV40184

1

SY01200_D_CDC

SY01200

1

SY01200_IU_CDC

SY01200

1

UPR00100_D_CDC

UPR00100

1

UPR00100_IU_CDC

UPR00100

1

UPR00300_D_CDC

UPR00300

1

UPR00300_IU_CDC

UPR00300

1

UPR00400_D_CDC

UPR00400

1

UPR00400_IU_CDC

UPR00400

1

UPR00700_D_CDC

UPR00700

1

UPR00700_IU_CDC

UPR00700

1

UPR40300_D_CDC

UPR40300

1

UPR40300_IU_CDC

UPR40300

1

UPR40301_D_CDC

UPR40301

1

UPR40301_IU_CDC

UPR40301

1

UPR40600_D_CDC

UPR40600

1

UPR40600_IU_CDC

UPR40600

1

UPR40700_D_CDC

UPR40700

1

UPR40700_IU_CDC

UPR40700

1

UPR41100_D_CDC

UPR41100

1

UPR41100_IU_CDC

UPR41100

1

WS10101_D_CDC

WS10101

1

WS10101_IU_CDC

WS10101

1

WS40000_D_CDC

WS40000

1

WS40000_IU_CDC

WS40000

1

WS41001_D_CDC

WS41001

1

WS41001_IU_CDC

WS41001

1

WSDOCS_D_CDC

WSDOCS

1

WSDOCS_IU_CDC

WSDOCS

1

  • No labels