尝试在POSTGRESQL中使用触发器自动插入表

hs1ihplo 于 2023-02-12 发布在 PostgreSQL

关注(0)|答案(1)|浏览(143)

我正在尝试创建一个触发器和函数，用于将已插入表customers中的值插入到表purchase中。
表customers的列
1-customer_id序列主键在采购中引用customer_id
2-c_名称VARCHAR
3-数量双精度
表购买的列
1-customer_id序列主键2金额双精度
触发器和函数的代码：

CREATE OR REPLACE FUNCTION auto_insert_purchases()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$body$
BEGIN
insert into purchases(customer_id,purchase) values
(NEW.customer_id,NEW.purchase);
END
$body$
CREATE TRIGGER tr_auto_insert_purchases
AFTER INSERT ON customers
EXECUTE PROCEDURE auto_insert_purchases()

正如您所看到的，它应该获取新行数据并将其插入到表中，但在这样做并插入到customers之后：

insert into customers values(2,'Stewie Griffin',4.99);

我收到此错误消息：

ERROR:  null value in column "customer_id" of relation "purchases" violates not-null 
constraint
DETAIL:  Failing row contains (null, null).
CONTEXT:  SQL statement "insert into purchases(customer_id,purchase) values
(NEW.customer_id,NEW.purchase)"
auto_insert_purchases() PL/pgSQL fonksiyonu, 3. satır, SQL ifadesi içinde
SQL state: 23502

为什么失败的行包含空值？我使用的NEW关键字不正确吗？

postgresql

来源：https://stackoverflow.com/questions/75412420/trying-to-automatically-insert-into-a-table-using-triggers-in-postgresql

1条答案

按热度按时间

niwlg2el1#

CREATE TABLE customers (
    customer_id int4 NULL,
    c_name varchar NULL,
    amount float8 NULL
);

CREATE TABLE purchases (
    customer_id int4 NULL,
    amount float8 NULL
);

CREATE OR REPLACE FUNCTION auto_insert_purchases()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
    insert into purchases(customer_id, amount) values
    (NEW.customer_id, NEW.amount);
    return new; 
END;
$function$
;

create trigger tr_auto_insert_purchases 
after insert ON customers
for each row 
    execute procedure auto_insert_purchases();
       

insert into customers(customer_id, c_name, amount) values (2,'Stewie Griffin', 4.99);

select * from purchases;
-- Result:
 
customer_id|amount|
-----------+------+
          2|  4.99|

您可能只是忘记在CREATE TRIGGER tr_auto_insert_purchases AFTER INSERT ON customers之后编写for each row语句

赞(0）回复(0）举报 2023-02-12

我来回答

尝试在POSTGRESQL中使用触发器自动插入表

1条答案

相关问题

热门标签

最新问答