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.
Rushikesh Dattatray Bane (Inactive)
added a comment - 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
As per the new script I have created the triggers for following tables,
address
assignment
company
codelist
companysourceproductmapping
dmscustomfieldmapping
dmsfields
document
email
emailaction
emailplaceholder
phone
sourceproductuser
sourcesystemfieldlabel
sourcesystemfields
users
person
assignmentcustomattributedata
personcustomattributedata
For each table 3 triggers are created with name like
Trigger_tablename_Insert
Trigger_tablename_Update
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.
Sonali Farakate (Inactive)
added a comment - - edited Hi Rohan J Khandave ,
I have updated the trigger creation script shared by Rushikesh Dattatray Bane .
Changes Done:
Added column ModifiedUserID
Added SystemTime as per UTC
Separated History table creation part.
As per the new script I have created the triggers for following tables,
address
assignment
company
codelist
companysourceproductmapping
dmscustomfieldmapping
dmsfields
document
email
emailaction
emailplaceholder
phone
sourceproductuser
sourcesystemfieldlabel
sourcesystemfields
users
person
assignmentcustomattributedata
personcustomattributedata
For each table 3 triggers are created with name like
Trigger_tablename_Insert
Trigger_tablename_Update
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
DMS - Fall Sprint 8, DMS - Gap Fall & Win Sprint 1, DMS - Gap Fall & Win Sprint 2, DMS - Gap Fall & Win Sprint 3
[ 167, 173, 174, 177
]
DMS - Fall Sprint 8, DMS - Gap Fall & Win Sprint 1, DMS - Gap Fall & Win Sprint 2, DMS - Gap Fall & Win Sprint 3, DMS - Gap Fall & Win Sprint 4
[ 167, 173, 174, 177, 179
]
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