Создание в базе OTRS 5 хранимых процедур, для удаления не нужных заявок

Создаем хранимую процедуру для удаления строк в таблице article

Актуально для OTRS 5, для версии 6

DeleteArticleByID
CREATE DEFINER=`root`@`%` PROCEDURE `DeleteArticleByID`(IN ID INT)
BEGIN
-- Удаляем article
START TRANSACTION;
-- Provide the values of the primary key of the row to delete.
SET @id_to_delete = ID;
DELETE FROM article_attachment USING article_attachment,
 article 
WHERE
 `article`.`id` = `article_attachment`.`article_id`
 AND article.id = @id_to_delete;
DELETE FROM article_flag USING article_flag,
 article 
WHERE
 `article`.`id` = `article_flag`.`article_id`
 AND article.id = @id_to_delete;
DELETE FROM article_plain USING article_plain,
 article 
WHERE
 `article`.`id` = `article_plain`.`article_id`
 AND article.id = @id_to_delete;
DELETE FROM ticket_history USING ticket_history,
 article 
WHERE
 `article`.`id` = `ticket_history`.`article_id`
 AND article.id = @id_to_delete;
DELETE FROM time_accounting USING time_accounting,
 article 
WHERE
 `article`.`id` = `time_accounting`.`article_id`
 AND article.id = @id_to_delete;
DELETE FROM article USING article 
WHERE
 article.id = @id_to_delete;
COMMIT;
SELECT 1;
END

Создаем хранимую процедуру для удаления строк в таблице ticket

DeleteTicketByID
CREATE DEFINER=`root`@`%` PROCEDURE `DeleteTicketByID`(IN ID INT)
BEGIN
-- Удаляем ticket
START TRANSACTION;
-- Provide the values of the primary key of the row to delete.
SET @id_to_delete = ID;

DELETE FROM ticket_flag
 USING ticket_flag, ticket
 WHERE `ticket`.`id` = `ticket_flag`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM ticket_history
 USING ticket_history, ticket
 WHERE `ticket`.`id` = `ticket_history`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM ticket_index
 USING ticket_index, ticket
 WHERE `ticket`.`id` = `ticket_index`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM ticket_lock_index
 USING ticket_lock_index, ticket
 WHERE `ticket`.`id` = `ticket_lock_index`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM ticket_watcher
 USING ticket_watcher, ticket
 WHERE `ticket`.`id` = `ticket_watcher`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM time_accounting
 USING time_accounting, ticket
 WHERE `ticket`.`id` = `time_accounting`.`ticket_id`
 AND ticket.id = @id_to_delete;
DELETE FROM ticket
 USING ticket
 WHERE ticket.id = @id_to_delete;
COMMIT;
SELECT 1;
END

 

 

 

 

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *