Can I use a db trigger to insert into a non out of the box jira table?

Question

Hi

I would like to create a trigger on the projectversion table that then inserts a value in a new table, which will be read by a service and send out notifications if changes have been made. We are currently on 4.2 and the version create event is not yet available, and I was asked to come up with an intermediate solution until we are ready to upgrade to 4.4.

I created the trigger, it compiled seemingly correctly, but when I went to the application to add a new version, it failed. I get an error with the following cause:

Cause: 
com.atlassian.jira.exception.DataAccessException: org.ofbiz.core.entity.GenericEntityException: while inserting: [GenericEntity:Version][id,12062][project,10378][description,][sequence,13][name,2.4.95][releasedate,2011-08-30 00:00:00.0] (SQL Exception while executing the following:INSERT INTO projectversion (ID, PROJECT, vname, DESCRIPTION, SEQUENCE, RELEASED, ARCHIVED, URL, RELEASEDATE) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?) (Cannot load from mysql.proc. The table is probably corrupted))

Can this be done? creating a trigger on a jira table? If so, then it might be that my syntax is incorrect... but I wanted to check first of all to see if this is even "allowed".

thank you so much!

-nicky

Nic Brough -Adaptavist- · Answer

No, JIRA talks to the database via a single user.  So unless the update specifically captures the user making it, you can't know who it is.

By "specifically captures", I mean if it is an issue edit or transition, then there is going to be a swathe of tables updated (all by jiradbuser) and one of those tables will be "changegroup".  The "author" going into "changegroup" will be the person making the change.  Similarly for comments - the "author" on jiraaction will be the user commenting.  But if watchers are changed, you won't get anything you can tie to a specific user, I think.

Arianna Fabbri · Answer

Hi @Nic Brough [Adaptavist] ,
while creating a trigger on a Jira db table (not to update anything but to check who is asking the change) is possibile to check the user that is currently asking the change?
Or in any case the user that make changes is the db user?

Thank you
Arianna

Nicky Van Bulck · Answer

Thanks Jamie and Nic. Jamiem you are correct, this is a trigger on a JIRA table that then inserts into a NON JIRA table, for auditing reasons.

The trigger seems to compile correctly, and the insert statement within it, executes fine by itself too, so I'm not sure what is wrong there. I logged a post at the mysql forum.

Here is the trigger syntax in case anyone here could offer some insight, I can use all the help I can get :)

-- Trigger DDL Statements
DELIMITER $$

USE `jira`$$

CREATE TRIGGER `jira`.`InsertVersionLog`
AFTER INSERT ON `jira`.`projectversion`
FOR EACH ROW
BEGIN
 INSERT INTO ProjectVersionLog (
    Timestamp,
    VersionName,
    ReleaseDate,
    Type,
    SentFlag
    )
    VALUES
    (now(),
    new.vname,
    new.releasedate,
    'Insert',
    'N');

END$$

Thank you!

-nicky