第二次触发结果消失 [英] Second trigger result disappear

问题描述

请告知触发器tr_stock_plus的结果是否消失了?因为在INSERT查询之后:

Please advise were result of trigger tr_stock_plus disappear? Because after INSERT query:

INSERT INTO test.purchase(
import_id, product_id, usd_price, qty)
VALUES (2, 'CG-003', 40, 40);

我的回复正常，没有错误消息:

I have normal reply, no error message:

INSERT 0 1
Query
returned successfully in 242 msec.

数据已插入

test.purchase

test.purchase

<身体>

import_id	product_id	usd_price	euro_price	数量
2	CG-003	33	33	40

第一个触发货币转换的方法也很有效，但第二个目标表没有变化

and first trigger for currency convertion also works well, but no changes in second targeted table

test.stock

test.stock

<身体>

product_id	stock_qty	stock_price	stock_amount
CG-003	null	null	null

---

    CREATE TABLE test.purchase
(
    import_id integer NOT NULL,
    product_id text COLLATE pg_catalog."default",
    usd_price numeric(10,2),
    euro_price numeric(10,2),
    qty integer
)

CREATE TRIGGER tr_p
    AFTER INSERT OR UPDATE OF usd_price
    ON test.purchase
    FOR EACH ROW
    EXECUTE PROCEDURE test.f_conv();

CREATE FUNCTION test.f_conv()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.purchase pr
SET euro_price = usd_price / i.rate
FROM  test.imports i
WHERE pr.import_id = i.import_id;
RETURN NEW;
END

CREATE TRIGGER tr_stock_plus
AFTER INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.f_stock_plus();

CREATE FUNCTION test.f_stock_plus()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
UPDATE test.stock s
SET stock_qty = stock_qty + 
(SELECT pr.qty
FROM test.purchase pr   
WHERE pr.product_id = s.product_id);
RETURN NEW;
END

CREATE TABLE test.stock
(
    product_id text COLLATE pg_catalog."default" NOT NULL,
    stock_qty numeric(10,0),
    stock_price numeric(10,2),
    stock_amt numeric(10,2),
    CONSTRAINT stock_pkey PRIMARY KEY (product_id)
)

推荐答案

使该过程更容易遵循的想法.参见末尾的评论.

Ideas to make the process simpler to follow. See comments at end.

CREATE TRIGGER tr_p
    BEFORE INSERT OR UPDATE OF usd_price
    ON test.purchase
    FOR EACH ROW
    EXECUTE PROCEDURE test.f_conv();

CREATE FUNCTION test.f_conv()
    RETURNS trigger
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
    conv_rate = numeric;
BEGIN
   SELECT INTO 
      conv_rate 
   FROM  
      test.imports i
   WHERE NEW.import_id = i.import_id;

   NEW.euro_price = usd_price / conv_rate;
   RETURN NEW;
END

CREATE TRIGGER tr_stock_plus
BEFORE INSERT
ON test.purchase
FOR EACH ROW
EXECUTE PROCEDURE test.f_stock_plus();

CREATE FUNCTION test.f_stock_plus()
RETURNS trigger

LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
   UPDATE 
      test.stock s
   SET 
      stock_qty = stock_qty + NEW.qty
   WHERE 
      NEW.product_id = s.product_id;
   RETURN NEW;
END

转换为 BEFORE 触发器，以允许将新值直接传递到表中.还避免了子选择.我也建议将列设置为 NOT NULL ，或者如果不提供 DEFAULT 值，例如数字/整数字段为0.

Convert to BEFORE triggers to allow for directly passing in new values to table. Also avoids sub-selects. I would also advise either setting columns to NOT NULL or if not providing a DEFAULT value e.g. 0 for numeric/integer fields.

这篇关于第二次触发结果消失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！