Hi Sonali Farakate,

The script for creating the History table along with triggers is completed.

I have attached the script in this Jira. Also, there are inline comments for using that query.

Create_Trigger_and_HistoryTable_MYSQL.sql

-------------------------------------------------------------------------------------------------

Naming Conventions  : 

For eg : 
Main Table - Communication
For this below will be the outcome from the script :

History table -  Communication_History
Triggers Name - trg_Communication_insert, trg_Communication_update, trg_Communication_delete

-------------------------------------------------------------------------------------------------

Below are some changes that I have done and impediments that I'm facing  : 

Changes Server Level :

Disable - secure-file-priv option - As we are writing out queries into a file and not creating directly, this feature needs to be disabled. I have disabled it for local server and will disable for rest of environments as required.

Impediments :

Queries are written into a file: As of now, we cannot have the drop and exists statements in a dynamic query as it is not a feature in MYSQL.
We have to change the path for writing out the queries in the file which will give us all the statements. If we want to pass the path as variable and parameter to SP, the query needs to be a prepared statement which will take time with analysis. 

We can have this in the further scope of development. Please review the script and let me know if any queries.

Regards,

Rushikesh

CC - Rohan J Khandave Samir Satya

Hi Rohan J Khandave,

I have updated the trigger creation script shared by Rushikesh Dattatray Bane.
Changes Done:

1. Added column ModifiedUserID
2. Added SystemTime as per UTC
3. Separated History table creation part.

As per the new script I have created the triggers for following tables,

1. address
2. assignment
3. company
4. codelist
5. companysourceproductmapping
6. dmscustomfieldmapping
7. dmsfields
8. document
9. email
10. emailaction
11. emailplaceholder
12. phone
13. sourceproductuser
14. sourcesystemfieldlabel
15. sourcesystemfields
16. users
17. person
18. assignmentcustomattributedata
19. personcustomattributedata

• For each table 3 triggers are created with name like

1. Trigger_tablename_Insert
2. Trigger_tablename_Update
3. Trigger_tablename_Delete

• Stored procedure 'CreateTriggersForTable' will generate the triggers to insert data in history table.

How to use SP CreateTriggersForTable?
1. Inputs : dbName,tableName
2. Change : Need to update the value of OUTFILE in SP as per the requirement(i.e. need to provide the folder path with filename.sql) 
3. Output : This SP will create a file(with provided name in OUTFILE) in which we get the 3 triggers generated for history table.

NOTE :
Need to create 3 different files of these with above mentioned naming convention and check-in into '\DMS\Databases Objects\05_Triggers' folder.
Need to generate these triggers each time on ALTER table and update the existing triggers with new.

Thanks,
Sonali Farakate

 CC : Samir 

No QA inputs required. So, marking jira done.