Pass variables to Procedure or Trigger in MySQL

[Ramblin]

The good news is, I don't feel so stupid

The bad news is, I think I stumped the Sugar community with the question posed in
http://www.sugarcrm.com/forums/f6/au...p-table-77105/
about auditing fields in a relationship table so I am trying a different way

It was suggested that I create Triggers in MySQL to have MySQL handle the audit of the fields in the relationship table.

In a test environment using a dbase named "test" (I know, how creative), with a table "example", I have successfully created an audit table (example_audit) with the same field definitions as in SugarCRM's audit tables and I have successfully created the triggers in the example table:
- After Insert
- After Update
- After Delete
which write to the example_audit table whenever changes are made to the fields in the example table.
(see below for code; big credit to
Database Transaction Auditing With MySQL and PostgreSQL Using Triggers
for showing me the way)

However, I do not know how to pass to MySQL, either to a Procedure or a Trigger, the variables I need to properly log the changes
- UserID of the user making the change (created_by in the audit table)
- RecordID of the ID from the example table of the record being changed (parent_id in the audit table)

Can anyone tell me what I need to put into Sugar script and where to properly pass these variables?

Ramblin

Code for the After Insert Trigger

DROP TRIGGER IF EXISTS `test_example_ai_audit`//
CREATE TRIGGER `test_example_ai_audit` AFTER INSERT ON `example`
 FOR EACH ROW BEGIN
    IF (NEW.name <> '') AND (NEW.name IS NOT NULL) THEN
        INSERT INTO `test`.`example_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"name","VARCHAR(30)","---new_record---",NEW.name,NULL,NULL);
    END IF;
    IF (NEW.age <> 0) AND (NEW.age IS NOT NULL) THEN
        INSERT INTO `test`.`example_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"age","INT(11)","---new_record---",NEW.age,NULL,NULL);
    END IF;
END
//

Code for the After Update Trigger

DROP TRIGGER IF EXISTS `test_example_au_audit`//
CREATE TRIGGER `test_example_au_audit` AFTER UPDATE ON `example`
 FOR EACH ROW BEGIN
    IF (NEW.name <> OLD.name) OR (NEW.name IS NOT NULL AND OLD.name IS NULL) OR (NEW.name IS NULL AND OLD.name IS NOT NULL) THEN
        INSERT INTO `test`.`example_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"name","VARCHAR(30)",OLD.name,NEW.name,NULL,NULL);
    END IF;
    IF (NEW.age <> OLD.age) OR (NEW.age IS NOT NULL AND OLD.age IS NULL) OR (NEW.age IS NULL AND OLD.age IS NOT NULL) THEN
        INSERT INTO `test`.`example_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"age","INT(11)",OLD.age,NEW.age,NULL,NULL);
    END IF;
END
//

Code for the After Delete Trigger

DROP TRIGGER IF EXISTS `test_example_ad_audit`//
CREATE TRIGGER `test_example_ad_audit` AFTER DELETE ON `example`
 FOR EACH ROW BEGIN
    INSERT INTO `test`.`example_audit`
        (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
        VALUES
        (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"name","VARCHAR(30)",OLD.name,"---deleted_record---",NULL,NULL);
    INSERT INTO `test`.`example_audit`
        (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
        VALUES
        (LAST_INSERT_ID(),CONCAT(CURRENT_USER(),USER()),"age","INT(11)",OLD.age,"---deleted_record---",NULL,NULL);
END
//

[Ramblin]

I have it all working with one exception: I cannot figure out how to capture and put into the audit table the Sugar UserID.

See below for what I have done, which I am now using and it works fine, with the above caveat.

This is useful when you want the auditing to enable MySQL queries which need dependable information on changes to the custom fields added to a relationship table (like the contact_role in contacts_opportunities). It does NOT allow you to see, from withing Sugar, the list of changes made, since this auditing is handled outside of Sugar and Sugar is therefore blind to the auditing.

If you can figure out either
- how to pass the Sugar UserID to the MySQL trigger
- how to audit a custom field in a relationship table using native SugarCRM capabilities
PLEASE feel free to let us know

To mimic what I have done, see below

===================

5 steps:
1) Create an audit table to capture the changes made to the source table
2) Create a trigger on the audit table to automatically generate a UUID() for each record entry
3) Create 3 triggers on the source table to log to the audit table any changes made to the source table whenever
3a A new relationship is created
3b An existing relationship is edited, and the custom fields are changed
3c An existing relationship is deleted (either by "Removing" the relationship from within SugarCRM or deleting the record from within MySQL)

For this example, I have
CRM MySQL dbase: ayudacrm
relationship table containing custom fields to be audited: evmgr_ev_contacts_c
custom field1: evmgr_ev_contacts_contact_ev_role
custom filed2: evmgr_ev_contacts_contact_ev_hours
auditing table created: evmgr_ev_contacts_c_audit

1) Create the audit table
There are numerous structures you could create with audit tables, but for simplicity and maintenance, I created the same structure SugarCRM uses when it creates an audit table
Run a MySQL command

DELIMITER $$
USE `ayudacrm` $$
DROP TABLE IF EXISTS `evmgr_ev_contacts_c_audit` $$
CREATE TABLE `evmgr_ev_contacts_c_audit` 
(
id CHAR(36),
parent_id CHAR(36) NOT NULL,
date_created TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by VARCHAR(36),
field_name VARCHAR(100),
data_type VARCHAR(100),
before_value_string VARCHAR(255),
after_value_string VARCHAR(255),
before_value_text TEXT,
after_value_text TEXT,
PRIMARY KEY(id),
KEY(parent_id)
)
CHARACTER SET utf8 COLLATE utf8_general_ci $$
DELIMITER ;

2) Add the trigger to the audit table to auto-generate a UUID() for each new record created in the audit table
Run a MySQL command

DELIMITER $$ 
USE `ayudacrm` $$
DROP TRIGGER IF EXISTS evmgr_ev_contacts_c_audit_bi_id $$ 
CREATE TRIGGER evmgr_ev_contacts_c_audit_bi_id BEFORE INSERT ON evmgr_ev_contacts_c_audit FOR EACH ROW 
    BEGIN 
        SET NEW.id = UUID() ;
    END; $$
DELIMITER ;

3a) Add the trigger to the source table to log any new records created in the source/relationship table
Run a MySQL command

DELIMITER $$
USE `ayudacrm` $$
DROP TRIGGER IF EXISTS `evmgr_ev_contacts_c_ai_audit` $$
CREATE TRIGGER `evmgr_ev_contacts_c_ai_audit` AFTER INSERT ON `ayudacrm`.`evmgr_ev_contacts_c` 
FOR EACH ROW
BEGIN
    IF (NEW.evmgr_ev_contacts_contact_ev_role <> '') AND (NEW.evmgr_ev_contacts_contact_ev_role IS NOT NULL) THEN
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (NEW.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_role","VARCHAR(24)","---new_record---",NEW.evmgr_ev_contacts_contact_ev_role,NULL,NULL);
    END IF;
    IF (NEW.evmgr_ev_contacts_contact_ev_hours <> 0) AND (NEW.evmgr_ev_contacts_contact_ev_hours IS NOT NULL) THEN
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (NEW.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_hours","VARCHAR(24)","---new_record---",NEW.evmgr_ev_contacts_contact_ev_hours,NULL,NULL);
    END IF;
END;$$
DELIMITER ;

3b) Add the trigger to the source table to log any changes made to the custom fields in the source/relationship table
Note that you have to treat a relationship "Removede" from within Sugar as a MySQL update not a MySQL delete
since the record is not actually being deleted, it is having one of its fields' (deleted) values set to 1
Run a MySQL command

DELIMITER $$
USE `ayudacrm` $$
DROP TRIGGER IF EXISTS `evmgr_ev_contacts_c_au_audit` $$
CREATE TRIGGER `evmgr_ev_contacts_c_au_audit` AFTER UPDATE ON `ayudacrm`.`evmgr_ev_contacts_c`
FOR EACH ROW
BEGIN
    IF (NEW.deleted = 1 AND OLD.deleted = 0) THEN
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit` 
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (OLD.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_role","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_role,"---deleted_record---",NULL,NULL);
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit` 
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (OLD.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_hours","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_hours,"---deleted_record---",NULL,NULL);
    END IF;
    IF (NEW.deleted = 0 AND ((NEW.evmgr_ev_contacts_contact_ev_role <> OLD.evmgr_ev_contacts_contact_ev_role) OR (NEW.evmgr_ev_contacts_contact_ev_role IS NOT NULL AND OLD.evmgr_ev_contacts_contact_ev_role IS NULL) OR (NEW.evmgr_ev_contacts_contact_ev_role IS NULL AND OLD.evmgr_ev_contacts_contact_ev_role IS NOT NULL))) THEN
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (NEW.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_role","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_role,NEW.evmgr_ev_contacts_contact_ev_role,NULL,NULL);
    END IF;
    IF (NEW.deleted = 0 AND ((NEW.evmgr_ev_contacts_contact_ev_hours <> OLD.evmgr_ev_contacts_contact_ev_hours) OR (NEW.evmgr_ev_contacts_contact_ev_hours IS NOT NULL AND OLD.evmgr_ev_contacts_contact_ev_hours IS NULL) OR (NEW.evmgr_ev_contacts_contact_ev_hours IS NULL AND OLD.evmgr_ev_contacts_contact_ev_hours IS NOT NULL))) THEN
        INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit`
            (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
            VALUES
            (NEW.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_hours","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_hours,NEW.evmgr_ev_contacts_contact_ev_hours,NULL,NULL);
    END IF;
END;$$
DELIMITER ;

3c) Add the trigger to the source table to log any records that are deleted from the source/relationship table
Note that this trigger would only fire is a record was actually deleted from within MySQL
Run a MySQL command

DELIMITER $$
USE `ayudacrm` $$
DROP TRIGGER IF EXISTS `evmgr_ev_contacts_c_ad_audit` $$
CREATE TRIGGER `evmgr_ev_contacts_c_ad_audit` AFTER DELETE ON `ayudacrm`.`evmgr_ev_contacts_c` 
FOR EACH ROW
BEGIN
    INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit` 
        (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
        VALUES
        (OLD.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_role","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_role,"---deleted_record---",NULL,NULL);
    INSERT INTO `ayudacrm`.`evmgr_ev_contacts_c_audit` 
        (`parent_id`,`created_by`,`field_name`,`data_type`,`before_value_string`,`after_value_string`,`before_value_text`,`after_value_text`)
        VALUES
        (OLD.id,CONCAT(CURRENT_USER(),USER()),"evmgr_ev_contacts_contact_ev_hours","VARCHAR(24)",OLD.evmgr_ev_contacts_contact_ev_hours,"---deleted_record---",NULL,NULL);
END;$$
DELIMITER ;

That's it!

You have an audit table logging changes, additions or deletions to custom fields in a relationship table

[kdhadmin]

did you ever get the UserID issue answered ?

[Ramblin]

did you ever get the UserID issue answered ?

No,

If you do get an answer, please post back to this thread