how do we provide coldfusion's session value in to triggers in mysql5?

Question

while implementing triggers in mysql5 can i pass any session value that is in my coldfusion application (like- userName, createdBy etc) .

CREATE TRIGGER Users_After_Insert
AFTER insert ON users
FOR EACH ROW
INSERT INTO auditlog (RequisitionID,ApplicantID,ColumnName,OldID,NewID,Description,CreatedBy,CreatedDate)
values
(0,0,'',0,0,CONCAT('New user with UserID ',new.UserID,' is created.'),
 session.userName, curdate()
);

in above query i have to provide "session.userName" value to "CreatedBy" column of auditLog table because "CreatedBy" column is not in "user" table thats why i have to provide it from coldfusion session.

if some one could please help to solve this problem it would be greately appreciated.

Thanks
Yugal

Answer 1

Create a user defined variable and use this one in your trigger:

CREATE TRIGGER Users_After_Insert
AFTER insert ON users
FOR EACH ROW
INSERT INTO auditlog (RequisitionID,ApplicantID,ColumnName,OldID,NewID,Description,CreatedBy,CreatedDate)
values
(0,0,'',0,0,CONCAT('New user with UserID ',new.UserID,' is created.'),
 @userName, curdate()
);

before you execute the INSERT, you first have to set the username:

SET @userName = 'Shooter';
INSERT INTO ...;

But why don't you use a stored procedure for this? With a trigger, things can go wrong, you could forget to set the variable.