Ticket - Order Table and Triggers MySql

--
-- Table structure for table `uh46v_order_cart_ids`
--

DROP TABLE IF EXISTS `uh46v_order_cart_ids`;
CREATE TABLE `uh46v_order_cart_ids` (
  `ticket_id` int(11) unsigned NOT NULL COMMENT 'ticket id from OsTicket system',
  `order_id` int(11) unsigned NOT NULL COMMENT 'order id from open cart orders'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Joins Order from Open Cart (jooCart) to OsTicket ticket system';

--
-- Indexes for dumped tables
--

--
-- Indexes for table `uh46v_order_cart_ids`
--
ALTER TABLE `uh46v_order_cart_ids`
  ADD KEY `ticket_id` (`ticket_id`,`order_id`);

CREATE TRIGGER `uh46v_ticket_form_wo_insert` AFTER INSERT ON `cnost_form_entry_values`
 FOR EACH ROW BEGIN
set @OrderFieldID = (SELECT `value`
                                        FROM `uh46v_cart_ticket_settings_hash`
                                        WHERE `name` = 'osticket_workorder_cart_orderid'
                                        Limit 1);

If (@OrderFieldID IS NOT NULL) AND (New.field_id = @OrderFieldID) Then
    set @id = (SELECT Entry.object_id
    FROM `cnost_form_entry` as Entry
    WHERE Entry.Id = (SELECT EntryValues.entry_id FROM `cnost_form_entry_values` as EntryValues
    Where EntryValues.entry_id = New.entry_id
    AND EntryValues.value = New.value
        Limit 1)
    Limit 1);
    IF @id IS NOT NULL AND New.value IS NOT NULL AND New.value <> '' THEN
        INSERT INTO `uh46v_order_cart_ids`(`ticket_id`, `order_id`) VALUES (@id, New.value);
    End IF;
End IF;
END

CREATE TRIGGER `uh46v_ticket_form_wo_update` AFTER UPDATE ON `cnost_form_entry_values`
 FOR EACH ROW BEGIN
set @OrderFieldID = (SELECT `value`
                                        FROM `uh46v_cart_ticket_settings_hash`
                                        WHERE `name` = 'osticket_workorder_cart_orderid'
                                        Limit 1);
If (@OrderFieldID IS NOT NULL) AND (New.field_id = @OrderFieldID) THEN
    set @id = (SELECT Entry.object_id
    FROM `cnost_form_entry` as Entry
    WHERE Entry.Id = (SELECT EntryValues.entry_id FROM `cnost_form_entry_values` as EntryValues
    Where EntryValues.entry_id = New.entry_id
    AND EntryValues.value = New.value
        Limit 1)
    Limit 1);
    IF @id IS NOT NULL THEN
        set @existID = (SELECT `ticket_id`
            FROM `uh46v_order_cart_ids`
            WHERE `ticket_id` = @id
            LIMIT 1);
        IF @existID IS NULL THEN
            IF New.value IS NOT NULL THEN
                INSERT INTO `uh46v_order_cart_ids`(`ticket_id`, `order_id`) VALUES (@id, New.value);
            END IF;
        ELSE
            IF New.value IS NULL OR New.value = '' THEN
                DELETE FROM `uh46v_order_cart_ids`WHERE `ticket_id` = @existID;
            ELSE
                UPDATE `uh46v_order_cart_ids` SET `order_id`=New.value
                WHERE `ticket_id` = @id;
            END IF;
        End IF;
    End IF;
End IF;
END

CREATE TRIGGER `uh46v_ticket_form_wo_delete` BEFORE DELETE ON `cnost_form_entry_values`
 FOR EACH ROW BEGIN
set @OrderFieldID = (SELECT `value`
                                        FROM `uh46v_cart_ticket_settings_hash`
                                        WHERE `name` = 'osticket_workorder_cart_orderid'
                                        Limit 1);
If (@OrderFieldID IS NOT NULL) AND (old.field_id = @OrderFieldID) Then
    set @id = (SELECT Entry.object_id
    FROM `cnost_form_entry` as Entry
    WHERE Entry.Id = (SELECT EntryValues.entry_id FROM `cnost_form_entry_values` as EntryValues
    Where EntryValues.entry_id = old.entry_id
    AND EntryValues.value = old.value
        Limit 1)
    Limit 1);
    IF @id IS NOT NULL THEN
        DELETE FROM `uh46v_order_cart_ids`WHERE `ticket_id` = @id;
    End IF;
End IF;
END