USE `pbxlogic_autodial`$$
CREATE DEFINER = CURRENT_USER TRIGGER `pbxlogic_autodial`.`stop_conversation` AFTER INSERT ON `event_stop_conversation` FOR EACH ROW
BEGIN
    DECLARE done INTEGER DEFAULT 0;

    DECLARE id INTEGER DEFAULT 0;
    DECLARE new_channel VARCHAR(100) DEFAULT NULL;
    DECLARE new_uniqueid VARCHAR(100) DEFAULT NULL;

    /*запрос на получение phone_num_id из call_progress на событие начала разговора*/
    DECLARE cursor_get_phone_num_id CURSOR FOR
    SELECT
        phone_num_id
    FROM
        call_progress
    WHERE
        channel = new_channel AND
        uniqueid = new_uniqueid;

    /*хендлер сигнализирующий об конце строк в каком-либо запросе*/
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

    SET done=0;
    SET new_channel=NEW.channel;
    SET new_uniqueid=NEW.uniqueid;

    OPEN cursor_get_phone_num_id;
        WHILE done=0 DO
            FETCH cursor_get_phone_num_id INTO id;
        END WHILE;
    CLOSE cursor_get_phone_num_id;

    IF id>0 THEN
        /*очищаем оператора */
        UPDATE operators
        SET
            phone_num_id=NULL,
            trash_found=0,
            garbage_collector_time = NULL
        WHERE
            phone_num_id=id;

        /*очищаем call_progress*/
        DELETE
        FROM
            call_progress
        WHERE
            phone_num_id=id;
    END IF;
END