MySQL Forums
Forum List  »  MySQL Administrator

Trigger not populating USER_CREATED and USER_MODIFIED with user connected
Posted by: Nathalie Lefrancois
Date: July 18, 2011 12:33PM

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

Options: ReplyQuote


Subject
Written By
Posted
Trigger not populating USER_CREATED and USER_MODIFIED with user connected
July 18, 2011 12:33PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.