Triggers Documentation
Currently our triggers allow us to track any database changes when an UPDATE or an INSERT
occurs for a table. Changes are only for select CEM and QUAD tables.
Definition: a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, OR DELETE statements on a table or a view.
Table of Contents
Trigger
- A trigger can be located in [Database_Name] > Tables > Table_Name > Triggers > Trigger_Name
- ie.
[Core_CEM]Database > Tables >smart.sam_SetupUser> Triggers >TR_sam_SetupUser
- ie.
Change History Table
The following table will save all UPDATE or INSERT data when a database change is made.
smart.sam_ChangeHistory- PrimaryKey: Depending which table is being modified, the PrimaryKey value will either be the actual PrimaryKey or a ForeignKey.
- ie.
smart.sam_User_JobTypehas both UniqueNo (PK) and UserNo (FK). Saving the UniqueNo (PK) in this case provides little to no value because we do not know which user has been affected. Using the UserNo (FK) value specifies exactly which user and fields changed.
- ie.
- PrimaryKey: Depending which table is being modified, the PrimaryKey value will either be the actual PrimaryKey or a ForeignKey.
Tables with Triggers
The following tables UPDATE or INSERT changes will be tracked in the smart.sam_ChangeHistory table.
smart.p11_Samplesmart.p11_SampleRoutingsmart.p11_SampleRoutingRulesmart.p11_SampleRouting_BuddyGroup- ForeignKey: SampleRoutingNo
smart.p11_User_JobType- ForeignKey: UserNo
smart.pbc_SampleTablesmart.sam_SetupUsersmart.sam_User_JobType- ForeignKey: UserNo
smart.sam_User_Menu- ForeignKey: UserNo
Trigger Scripts
Example script with PrimaryKey
Example script with using ForeignKey value as the PrimaryKey indicator
A cursor is used to loop through a table to detect any changes via UPDATE or INSERT and saves the data to the smart.sam_ChangeHistory table.