创建触发器以更新分类/订单栏 [英] Creating a trigger to update a sort/order column

问题描述

我创建了这个触发器来更新seq列。我必须跟踪表中某些项目的顺序，但只有当liability_category_id = 1,2时才有效。所以我的订单是棘手的，因为任何与liability_category_id = 3的项目我不需要跟踪。

在我的触发器中，我查询找到最后输入的seq数字（使用max（seq）），然后转向并用seq + 1更新新条目。

  DELIMITER $$ 
 
 USE`analysisdb` $$ 
 
 DROP TRIGGER / *！50032 IF EXISTS * /`trigger_liability_detail_after_insert` $$ 
 
 CREATE 
 / *！50017 DEFINER ='admin'@'％'* / 
 TRIGGER`trigger_liability_detail_after_insert` AFTER INSERT ON`liability_detail` 
 FOR ROW BEGIN 
 DECLARE SortOrder INT; 
 IF NEW.liability_category_id = 1 OR NEW.liability_category_id = 2 THEN 
 
 SET SortOrder =（SELECT MAX（seq）FROM liability_detail WHERE analysis_id = new.analysis_id AND liability_category_id IN（1，2） ）; 
 UPDATE liability_detail SET seq =（SortOrder + 1）WHERE id = NEW.id; 
 END IF; 
 END; 
 $$ 
 
 DELIMITER; 
  

但是，当输入一个新项目时，出现以下错误：无法更新表格'stored_detail'在存储函数/触发器中，因为它已经被调用这个存储函数/触发器的语句所使用。

有没有更好的方法来控制这些项目的订购？我原先的想法是简单地设置第一个seq = 1，然后seq = 2等。虽然排序重置为每个新的analysis_id。

解决方案 div>

我认为解决方法是让它成为一个before触发器，并在插入之前更新插入自身的记录。

所以

  CREATE 
 / *！50017 DEFINER ='admin'@'％'* / 
 TRIGGER`trigger_liability_detail_after_insert`在插入之前`liability_detail `
对于每行开始
 DECLARE SortOrder INT; 
 IF NEW.liability_category_id = 1 OR NEW.liability_category_id = 2 THEN 
 
 SET NEW.seq = 1 + IFNULL（（SELECT MAX（seq）FROM liability_detail WHERE analysis_id = new.analysis_id AND liability_category_id IN（1,2）），0）; 
 END IF; 
 END; 
 $$ 
  

这是一个快速复制/粘贴，但它应该是沿着这些行。

I have created this trigger to update the seq column. I have to keep track of the order of certain items in the table, but only if the liability_category_id = 1,2. So my ordering is tricky because any item with a liability_category_id = 3 I don't need to track.

In my trigger, I'm querying to find the last entered seq number (using max(seq)), then turning around and updating the new entry with the seq + 1.

DELIMITER $$

USE `analysisdb`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trigger_liability_detail_after_insert`$$

CREATE
/*!50017 DEFINER = 'admin'@'%' */
TRIGGER `trigger_liability_detail_after_insert` AFTER INSERT ON `liability_detail` 
    FOR EACH ROW BEGIN
    DECLARE SortOrder INT;
    IF NEW.liability_category_id = 1 OR NEW.liability_category_id = 2 THEN

    SET SortOrder = (SELECT MAX(seq) FROM liability_detail WHERE analysis_id = new.analysis_id AND liability_category_id IN (1, 2));
    UPDATE liability_detail SET seq = (SortOrder + 1) WHERE id = NEW.id;
    END IF;
    END;
$$

DELIMITER ;

However, when entering a new item, I get this error: Can't update table 'liability_detail' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Is there a better way to control the ordering of these items? My original thought was to simply set the first seq = 1, then seq = 2, etc. The ordering is reset for each new analysis_id though.

解决方案

I think the workaround is to make this a before trigger and update the record being insert itself prior to insert.

So

CREATE
/*!50017 DEFINER = 'admin'@'%' */
TRIGGER `trigger_liability_detail_after_insert` BEFORE INSERT ON `liability_detail` 
    FOR EACH ROW BEGIN
    DECLARE SortOrder INT;
    IF NEW.liability_category_id = 1 OR NEW.liability_category_id = 2 THEN

    SET NEW.seq = 1 + IFNULL((SELECT MAX(seq) FROM liability_detail WHERE analysis_id = new.analysis_id AND liability_category_id IN (1, 2)), 0);
    END IF;
    END;
$$

That was a quick copy/paste, but it should be something along those lines.

这篇关于创建触发器以更新分类/订单栏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！