<p>One option for doing this is to create a database trigger.</p>
<h2>In Mysql</h2>
<p>In MySQL and MariaDB, you can do this with mysql-udf-http. <a href="https://stackoverflow.com/a/57937476/3127174">From perodriguezl answer</a> from Stack Overflow:</p>
<blockquote>
<p>You can use a mysql-udf-http and then create a trigger like this:</p>
</blockquote>
<pre><code>delimiter $$
CREATE TRIGGER upd_check BEFORE UPDATE ON account 
FOR EACH ROW 
  BEGIN 
    IF NEW.amount &gt; 0 THEN 
      set @json = select json_object(account_id,amount) 
      select http_post('http://restservice.example.com/account/post',@json); 
    END IF; 
  END;$$ 

delimiter;
</code></pre>
<h2>In Postgres</h2>
<p>In Postgres, you can write a stored procedure and use <code>plperlu</code> with <code>REST::Client</code></p>
<p>From <a href="https://stackoverflow.com/a/46543582">JustMe's Answer</a> on Stack Overflow:</p>
<blockquote>
<p>...the fastest way is to use <code>plperlu</code> with <code>REST::Client</code> package, e.g.:</p>
</blockquote>
<pre><code>CREATE OR REPLACE FUNCTION restful.put(auri character varying, ajson_text text)
 RETURNS text
 LANGUAGE plperlu
 SECURITY DEFINER
AS $function$
  use REST::Client;  
  use Encode qw(encode);
  my $client = REST::Client-&gt;new();    
  $client-&gt;getUseragent()-&gt;proxy( 'https', 'http://some-proxy/' ); # use for proxy authentication
  $client-&gt;addHeader('Content-Type', 'application/json');          # headers
  $client-&gt;POST( $_[0], encode('UTF-8', $_[1]));                   # encoding
  return $client-&gt;responseContent();  
$function$
</code></pre>
<h2>Using Oracle</h2>
<p>On an Oracle database, You will need to use <code>UTL_HTTP</code> - <a href="https://technology.amis.nl/database/invoke-a-rest-service-from-plsql-make-an-http-post-request-using-utl_http-in-oracle-database-11g-xe/" rel="nofollow noreferrer">Lucas Jellema's blog has a good example.</a> There are also several others available.</p>
<h2>Conclusion</h2>
<p>In each case, You would then presumably want to tweak your trigger to watch for an <code>update</code> statement on the table and check for the status you want to trigger off of (eg, you probably don't want to trigger off of new/closed) as well as make sure it is for the right project (Not quite sure how JIRA's schema is laid out - it might do one table per project. Eg, you probably don't want to trigger builds off of IT's tickets, but only off of dev or QA's JIRA project if you have multiple.)</p>

CC BY-SA 4.0
2021-01-18T23:05:43.153Z
We don't have MySQL installed on any of the relevant servers. We don't want to set a status in JIRA, we want to trigger a build in GitLab.
jalanb
CC BY-SA 4.0
2021-01-19T02:42:32.333Z
@jalanb - I understand you do not want to set a status in JIRA. When your JIRA status transitions to something like "Ready for Testing", you want Gitlab CI to kick of a build and/or testing suite. You can do this with a REST call to Gitlab from your JIRA database server using a store procedure upon transition when a user changes the state in JIRA.
James Shewey
CC BY-SA 4.0
2021-01-19T23:22:03.823Z
Valid answer. you should upvote
schmichri
CC BY-SA 4.0
2021-01-21T22:01:05.510Z
We use JIRA Cloud, and do not have access to a "JIRA database server". Seems like overkill anyway, when I can just "Tigger a Webhook" from the relevant Transition page in the UI. The question is about the "REST call to Gitlab", not JIRA
jalanb
CC BY-SA 4.0
2021-01-18T16:56:06.833Z
2021-01-19T02:41:34.363Z
2021-01-19T02:41:34.363Z
James Shewey
<p>I didn't get what's wrong with <a href="https://developer.atlassian.com/server/jira/platform/webhooks/" rel="nofollow noreferrer">webhooks</a>
You just create a webhook on Gitlab CI side and provide this URL to your Jira account.</p>
<pre><code>https://gitlab.com/api/v4/projects/PROJECT_ID/ref/REF_NAME/trigger/pipeline?token=TOKEN
</code></pre>
<p><code>PROJECT_ID</code> - unique project id on Gitlab</p>
<p><code>REF_NAME</code> - your <code>qa</code> / <code>stage</code> / <code>beta</code> branches within repository</p>
<p><code>TOKEN</code> - uniquely generated access token (gitlab provided)</p>
<p><a href="https://docs.gitlab.com/ee/ci/triggers/" rel="nofollow noreferrer">Look here</a> for more details how to trigger build on Gitlab via this call</p>

CC BY-SA 4.0
2021-01-21T21:56:50.650Z
Thanks - that seems to be exactly what we need.
jalanb
CC BY-SA 4.0
2021-01-20T08:14:41.617Z
2021-01-20T08:14:41.617Z
Most Wanted
<p>I would like to start builds in <code>GitLab CI</code> in response to <code>issue transitions</code> in <code>JIRA</code>.</p>
<p>I have not found a direct way to do that from searching online. I have found ways to get GitLab to tell JIRA what to do, but seemingly no solutions in the other direction.</p>
<p>We have a constraint which would make <code>JIRA</code> to <code>GitLab</code> messages very helpful: we must deploy to servers under the rule that a branch gets deployed</p>
<ol>
<li>to ...test.example.com when the JIRA issue has status <code>QA</code></li>
<li>to ...stage.example.com when the JIRA issue has status <code>Stage</code></li>
<li>to ...beta.example.com when the JIRA issue has status <code>Beta</code></li>
<li>to ....example.com when the JIRA issue has status <code>Done</code></li>
</ol>
<p>Note that we have added a <code>Merge Request</code> field to issues in our <code>JIRA</code>, so we know &quot;the branch for an issue&quot; (and &quot;the issues for a branch&quot;).</p>
<p>To satisfy this constraint we run a python script to do deploys for <code>QA</code>, <code>Stage</code>, <code>Beta</code> every hour in a crontab for every project. That script has too many top-level loops, and too many features (being old).</p>
<p>Instead I would prefer to have <code>JIRA</code> notify that an issue is ready for re-build, and then ask our <code>GitLab CI</code> to re-build the relevant <code>Merge Request</code>.</p>
<p>In a JIRA workflow status transitions can &quot;Trigger A Webhook&quot;, but it seems we will have to supply our own <code>REST api</code> to which <code>JIRA</code> can <code>post</code> messages, and which can then ping <code>GitLab</code>. That's a new internal app, which will need maintenance, and gain features, so we would like to find out if there are any other solutions.</p>

<gitlab><gitlab-ci-runner><jira>
How to get GitLab CI to rebuild on JIRA issue status change?
jalanb
# How to get GitLab CI to rebuild on JIRA issue status change?

(Asked by: username_0 on 2021-01-16)

I would like to start builds in `GitLab CI` in response to `issue transitions` in `JIRA`.

I have not found a direct way to do that from searching online. I have found ways to get GitLab to tell JIRA what to do, but seemingly no solutions in the other direction.

We have a constraint which would make `JIRA` to `GitLab` messages very helpful: we must deploy to servers under the rule that a branch gets deployed

1. to ...test.example.com when the JIRA issue has status `QA`
2. to ...stage.example.com when the JIRA issue has status `Stage`
3. to ...beta.example.com when the JIRA issue has status `Beta`
4. to ....example.com when the JIRA issue has status `Done`

Note that we have added a `Merge Request` field to issues in our `JIRA`, so we know "the branch for an issue" (and "the issues for a branch").

To satisfy this constraint we run a python script to do deploys for `QA`, `Stage`, `Beta` every hour in a crontab for every project. That script has too many top-level loops, and too many features (being old).

Instead I would prefer to have `JIRA` notify that an issue is ready for re-build, and then ask our `GitLab CI` to re-build the relevant `Merge Request`.

In a JIRA workflow status transitions can "Trigger A Webhook", but it seems we will have to supply our own `REST api` to which `JIRA` can `post` messages, and which can then ping `GitLab`. That's a new internal app, which will need maintenance, and gain features, so we would like to find out if there are any other solutions.

**Tags:** gitlab, gitlab-ci-runner, jira


---

## 2 answers

### Answer by username_3 on 2021-01-20. Score: 3 | Accepted answer

I didn't get what's wrong with webhooks
You just create a webhook on Gitlab CI side and provide this URL to your Jira account.
https://gitlab.com/api/v4/projects/PROJECT_ID/ref/REF_NAME/trigger/pipeline?token=TOKEN

PROJECT_ID - unique project id on Gitlab
REF_NAME - your qa / stage / beta branches within repository
TOKEN - uniquely generated access token (gitlab provided)
Look here for more details how to trigger build on Gitlab via this call

> **Comments:**
> **username_0**: Thanks - that seems to be exactly what we need.  

---

### Answer by username_1 on 2021-01-18. Score: 1

One option for doing this is to create a database trigger.
In Mysql
In MySQL and MariaDB, you can do this with mysql-udf-http. From perodriguezl answer from Stack Overflow:

You can use a mysql-udf-http and then create a trigger like this:

delimiter $$
CREATE TRIGGER upd_check BEFORE UPDATE ON account 
FOR EACH ROW 
  BEGIN 
    IF NEW.amount > 0 THEN 
      set @json = select json_object(account_id,amount) 
      select http_post('http://restservice.example.com/account/post',@json); 
    END IF; 
  END;$$ 

delimiter;

In Postgres
In Postgres, you can write a stored procedure and use plperlu with REST::Client
From JustMe's Answer on Stack Overflow:

...the fastest way is to use plperlu with REST::Client package, e.g.:

CREATE OR REPLACE FUNCTION restful.put(auri character varying, ajson_text text)
 RETURNS text
 LANGUAGE plperlu
 SECURITY DEFINER
AS $function$
  use REST::Client;  
  use Encode qw(encode);
  my $client = REST::Client->new();    
  $client->getUseragent()->proxy( 'https', 'http://some-proxy/' ); # use for proxy authentication
  $client->addHeader('Content-Type', 'application/json');          # headers
  $client->POST( $_[0], encode('UTF-8', $_[1]));                   # encoding
  return $client->responseContent();  
$function$

Using Oracle
On an Oracle database, You will need to use UTL_HTTP - Lucas Jellema's blog has a good example. There are also several others available.
Conclusion
In each case, You would then presumably want to tweak your trigger to watch for an update statement on the table and check for the status you want to trigger off of (eg, you probably don't want to trigger off of new/closed) as well as make sure it is for the right project (Not quite sure how JIRA's schema is laid out - it might do one table per project. Eg, you probably don't want to trigger builds off of IT's tickets, but only off of dev or QA's JIRA project if you have multiple.)

> **Comments:**
> **username_0**: We don't have MySQL installed on any of the relevant servers. We don't want to set a status in JIRA, we want to trigger a build in GitLab.  
> **username_1**: @username_0 - I understand you do not want to set a status in JIRA. When your JIRA status transitions to something like "Ready for Testing", you want Gitlab CI to kick of a build and/or testing suite. You can do this with a REST call to Gitlab from your JIRA database server using a store procedure upon transition when a user changes the state in JIRA.  
> **username_2**: Valid answer. you should upvote  
> **username_0**: We use JIRA Cloud, and do not have access to a "JIRA database server". Seems like overkill anyway, when I can just "Tigger a Webhook" from the relevant Transition page in the UI. The question is about the "REST call to Gitlab", not JIRA  

---

