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

[Nicky Van Bulck]

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

[JamieA]

From your question you are trying to insert into a table not governed by jira, which should be fine. From the error message - "Cannot load from mysql.proc" it just looks like it's failing to load or parse the stored proc.

But yes in principle it's fine, at a previous place I created one one the role actor tables... can't remember why now, maybe auditing or something. Worked fine IIRC.

Only caveat is tables not governed by jira don't get backed up when you backup to xml.

[Nicky Van Bulck]

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

[Nic Brough -Adaptavist-]

You can create triggers on Jira tables, but you absolutely can NOT use them to update the Jira data in the database.

I'm not exactly sure what you've set up here, but it looks like you've tried to amend the Jira data (adding a new version?), which will break it. Even if you hadn't missed the sequence update, you can't actually hack Jira data while it's running anyway.

[Arianna Fabbri]

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

[Nic Brough -Adaptavist-]

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.