Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion

osTicket v1.10 (stable) and Maintenance Release v1.9.15 are now available! Go get it now

MySQL Before Insert Trigger for changing task number

Hi,

I have created the following trigger through phpmyadmin. 

DROP TRIGGER IF EXISTS `generate_task_number_from_ticket`;
DELIMITER $$
CREATE TRIGGER `generate_task_number_from_ticket` 
BEFORE INSERT ON `ost_task` 
FOR EACH ROW 
BEGIN 
DECLARE ticketnumber,teamsinto varchar(15); 
IF NEW.object_id > 0 THEN  
SET ticketnumber = (SELECT number FROM ost_ticket WHERE ticket_id = NEW.object_id) ; 
SET teamsinto = (SELECT name FROM ost_department WHERE id = NEW.dept_id) ; 
SET NEW.number = CONCAT(ticketnumber,'.', teamsinto); 
END IF; 
END$$
DELIMITER ; 


Basically, what im trying to do is to change the task's number (if belongs to a ticket) to ticket's number + task's department name, so user can easily recognize where the task belongs and what was the department.

The problem with this trigger is that if i test it in phpmyadmin (run an insert query), trigger works fine, but If i create a new task from osticket, trigger is not firing and no changes are made.

Definer in trigger, is the same user I use in ost-config.php

Comments

  • This sounds like a MySQL question, not an osTicket one.
  • Hi ntozier,
    In phpmyadmin (where trigger is created), trigger works fine with manual insert in table. My problem is that trigger is not firing when insert is done by osTicket. 

    Perhaps someone else here uses "Before Insert" trigger and can help with that.  
  • and the likely hood that someone on the mysql forums or mariadb forums having done this is far greater.  So I repeat, this sounds like a mysql question to me.
  • Correct Syntax :

    CREATE TRIGGER `generate_task_number_from_ticket` 
    BEFORE INSERT ON `ost_task` 
    FOR EACH ROW 
    BEGIN
    IF NEW.object_id > 0 THEN
    SET @ticketnumber = (SELECT number FROM ost_ticket WHERE ticket_id = NEW.object_id) ; 
    SET @teamsinto = (SELECT name FROM ost_department WHERE id = NEW.dept_id) ; 
    SET NEW.number = CONCAT(@ticketnumber,'.', @teamsinto); 
    END IF; 
    END
  • @merovatis interesting idea, i had 1 question tho, how do you solve 2 tasks getting the same number?
  • Hi Micke1101,
    What do you mean "solve"? I don't see any problem having the same number. ticket number and task number are not primary/AI fields in database, their Id is used.

    Let me explain what Im trying to accomplish :
    1. A ticket is created from an employee that asks for X repairs + custom work in his office. That means that different teams of technician will be involved (electricians, workers, etc).  I have changed ticket pdf file to include a table in footer of page, in witch technicians are writing down by hand, their name and how many hours they worked. This pages are returning back to office for importing by office secretary (agent).  
    2. An easy way to add technicians and how many hours they worked for a ticket is to add them as agents (although they dont use computers) and add a task in the ticket for every technician that works for the ticket. In tasks form, i have added a field like "hoursworked" (number)
    I have also created private departments for every team of technicians so they can first select one from the department dropdown in new task popup and then assign a technician (agent).  I thought that i could use department / agent dropdown as dependent dropdowns but it seems that all agents are visible in dropdown, although they dont belong to the department previously selected. Anyway this is something that I will try to fix now that i have added this trigger.
    3. In Task list, agents can see right away what tasks belong to tickets and who worked.

  • Yea i obviously don’t know your workflow for this but like the idea of being able to add variables such as the department name to the tasknumber, but what i mean is what if i create 2 tasks in the same ticket for the same department, both will have the same number and cause issues for lookup functions, i realise this might not affect your particular was more curious.
Sign In or Register to comment.