Trigger not populating USER_CREATED and USER_MODIFIED with user connected
I am puzzled about why a before insert trigger I created doesn't seem to assign the user connected at the time of the insert to fields USER_CREATED and USER_MODIFIED (rather it keeps assigning the name of the user who created the trigger). See below...
--LOGIN with user 'creator' and run the following:
--table creation
--trigger creation
CREATE TABLE `crm`.`test` (
`ID` int(10) NOT NULL auto_increment COMMENT 'Record ID (autoincrement)',
`RECORD_TYPE_NAME` varchar(20) NOT NULL COMMENT 'Name of the type of records (e.g. Accommodaton Booking)',
`RECORD_TYPE_DESCRIPTION` varchar(50) NOT NULL COMMENT 'Description of the record type',
`PARENT_RECORD_TYPE_ID` int(11) default NULL COMMENT 'Non mandatory type of record that is parent (e.g. ITS Accommodation Booking could have Accommodation Booking as parent type). Used to override default validations set at the upper level (specified in the CRMINT_ VLDTN table)',
`DATETIME_CREATED` datetime NOT NULL default '1000-01-01 00:00:00' COMMENT 'Date and time when the record was created',
`USER_CREATED` varchar(20) NOT NULL default ' ' COMMENT 'Username of the user who created the record',
`DATETIME_MODIFIED` datetime NOT NULL default '1000-01-01 00:00:00' COMMENT 'Date and time when the record was modified',
`USER_MODIFIED` varchar(20) NOT NULL default ' ' COMMENT 'Username of the user who modified the record',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Meta data table for recognized record types for integration ';
CREATE TRIGGER `crm`.`test_BI` BEFORE INSERT ON `crm`.`test`
FOR EACH ROW SET NEW.DATETIME_CREATED = SYSDATE(), NEW.USER_CREATED = mid(current_user(), 1, locate('@',current_user())-1), NEW.DATETIME_MODIFIED = SYSDATE(), NEW.USER_MODIFIED = mid(current_user(), 1, locate('@',current_user())-1);
--Login as user 'userx' and run the following:
--INSERT rows
INSERT INTO `crm`.`test` (RECORD_TYPE_NAME,RECORD_TYPE_DESCRIPTION,PARENT_RECORD_TYPE_ID) VALUES ('Service Request','Service Request Main Record Type',1,);
INSERT INTO `crm`.`test` (RECORD_TYPE_NAME,RECORD_TYPE_DESCRIPTION,PARENT_RECORD_TYPE_ID, USER_CREATED,USER_MODIFIED, DATETIME_CREATED, DATETIME_MODIFIED) VALUES ('Service Request','Service Request Main Record Type',1,'userx','userx', '2011-01-01 00:00:00', '2011-01-01 00:00:00');
SELECT * FROM test;
--shows both rows with DATETIME_CREATED and DATETIME_MODIFIED = now
-- USER_CREATED and USER_MODIFIED = 'creator'
-- These two fields should show userx since it is the user connected for the insert.
--The following code in the trigger returns the proper value (userx) though...
select mid(current_user(), 1, locate('@',current_user())-1) from dual;
--shows
userx