使用存储过程中的值更新触发器中的表 [英] Update table in a trigger with values from stored procedure

问题描述

我想创建一个触发器，以使用过程值更新表。我希望触发器执行以下操作：

I wanted to make a trigger which updates a table with the values of a procedure. I want the trigger to do something like this:

DELIMITER $$
CREATE TRIGGER onInsertVillage AFTER INSERT ON Village
FOR EACH ROW
BEGIN
    UPDATE Village V SET V.xCoordinaat = x, V.yCoordinaat = y FROM getVrijePlaatsInMap();
END$$
DELIMITER ;

但这是行不通的。该过程顺便返回了x和y值。

But this doesn't work.. The procedure returns an x and y value by the way. Is there a possible way to make the trigger do it's job?

推荐答案

我认为触发器应适应 xCoordinaat 和 yCoordinat 插入表 Village 中的每个新村庄。如果是这种情况，我将使用在插入之前-触发器，该触发器可以访问要插入的相应村记录的值（在插入之前）。在没有示例的架构和存储过程代码的情况下，我编写了一个简化的程序演示了此方法：

I suppose that the trigger shall adapt xCoordinaat and yCoordinat for every new village inserted into table Village. If this is the case, I would use an BEFORE INSERT-trigger, which has access to the values of the respective village record to be inserted (before it is inserted). In absence of schema and stored procedure code of your example, I wrote a simplified program demonstrating this approach:

create table test (
    a int,
    b int,
    c int
);


CREATE PROCEDURE simpleproc (IN a int, OUT b INT, OUT c int)
BEGIN
  set b = a div 100;
  set c = a % 100;
END;

CREATE TRIGGER test_before_insert
BEFORE INSERT
   ON test FOR EACH ROW
BEGIN
   call simpleproc (new.a,new.b,new.c);
END;

insert into test (a,b,c) values (120,0,0), (210,0,0), (303,0,0);

这将产生：

  a | b | c
----|---|---
120 | 1 | 20
210 | 2 | 10
303 | 3 | 3

使方法行之有效可能会有些棘手；在后插入触发器中，您可以访问 .NEW 值，但不能对其进行修改。因此，您必须在表上进行更新，但是您必须以某种方式标识刚刚插入的村庄。

Getting your approach working might be a little bit more tricky; In an AFTER INSERT-trigger, you have access to the .NEW-values, but you cannot modify them; Hence, you have to do an update on the table, but you somehow have to identify the village that has just been inserted.

这篇关于使用存储过程中的值更新触发器中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！