个人中心
gpt4 book ai didi

java - 创建 PostgreSql 触发器来更新库存数量

转载 作者：行者123 更新时间：2023-12-01 12:01:24 26 4
gpt4 key购买 nike

我想为使用 Postgresql DBMS 版本 9.4 的库存管理系统创建触发器。它允许我计算库存产品的数量。所以，她就是我们需要的表格的架构。

produit(produitid(PK),qteInitiale,qteInStock,prixDachat,prixDevente) ; 
entreeDetaille(qteIn,prixDachat,remise,(produitId(FK),bonId(FK),(PK)));
sortieDetaille(qteOut,prixDeVente,remise,(produitId(FK),bonId(FK),(PK)));

The Stock quantity = produit.qteInitiale where id = ? + sum(qteIn) where  id = ? + sum(qteOut) where id = ?

需要在 entreeDetaille 或 sortieDetaille 表上发生插入事件后调用触发器。

我已经开始尝试，但对我不起作用:

    CREATE OR REPLACE FUNCTION updateqteinstock()
      RETURNS trigger AS
    $BODY$
    declare 
    qteInitiale money;
    qteAcheter money ;
    qteVendue money;
    qteEnStock money ; 
    produitID integer ;
    begin

    if TG_OP == "INSERT" then   
    produitID := NEW.produitid; 
    else
    produitID := OLD.produitid; 
    end if ;


    -- ramener la quantité Initiale de stock
    qteInitiale := select qteinitiale from produit where produitid = produitID ;

    -- ramener la quantité acheter
    qteAcheter := (select sum(qtein) from entreedetaille where produitid = produitID ); 

 -- ramener la quantité vendue
   qteVendue := select sum(qteOut) from sortieDetaille where produitid = produitID ; 

    -- calculate quantité qte en stock + quantité acheter + quantité vendue
    qteEnStock := qteInitiale + qteAcheter - qteVendue;

    -- update la quantite en stock ;
    update produit set qtestock = qteEnStock where produitid = produitID ; 

    if TG_OP == "INSERT" 
    return new;
    else 
    return old;
    end;
    $BODY$
      LANGUAGE plpgsql

我收到此错误:

    org.postgresql.util.PSQLException: ERROR: column "produitid" does not exist
  Où : PL/pgSQL function updateqteinstock() line 11 at assignment
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)
    at DAO.BonDachatDAO.create(BonDachatDAO.java:203)
    at CONTROLLER.BonDachatController.createBonDachat(BonDachatController.java:83)
    at VIEW.eventsManagers.BonDachatEventManager.saveBonDachat(BonDachatEventManager.java:108)
    at VIEW.eventsManagers.BonDachatEventManager.actionPerformed(BonDachatEventManager.java:79)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2346)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6527)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6292)
    at java.awt.Container.processEvent(Container.java:2234)
    at java.awt.Component.dispatchEventImpl(Component.java:4883)
    at java.awt.Container.dispatchEventImpl(Container.java:2292)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4898)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4533)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4462)
    at java.awt.Container.dispatchEventImpl(Container.java:2278)
    at java.awt.Window.dispatchEventImpl(Window.java:2739)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:746)
    at java.awt.EventQueue.access$400(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:697)
    at java.awt.EventQueue$3.run(EventQueue.java:691)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.awt.EventQueue$4.run(EventQueue.java:719)
    at java.awt.EventQueue$4.run(EventQueue.java:717)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:716)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)
org.postgresql.util.PSQLException: ERROR: column "porduitid" does not exist
  Où : PL/pgSQL function updateqteinstock() line 14 at assignment
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2198)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1927)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:255)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:561)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:419)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:365)
    at DAO.BonDachatDAO.create(BonDachatDAO.java:203)
    at CONTROLLER.BonDachatController.createBonDachat(BonDachatController.java:83)
    at VIEW.eventsManagers.BonDachatEventManager.saveBonDachat(BonDachatEventManager.java:108)
    at VIEW.eventsManagers.BonDachatEventManager.actionPerformed(BonDachatEventManager.java:79)
    at javax.swing.AbstractButton.fireActionPerformed(AbstractButton.java:2022)
    at javax.swing.AbstractButton$Handler.actionPerformed(AbstractButton.java:2346)
    at javax.swing.DefaultButtonModel.fireActionPerformed(DefaultButtonModel.java:402)
    at javax.swing.DefaultButtonModel.setPressed(DefaultButtonModel.java:259)
    at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(BasicButtonListener.java:252)
    at java.awt.Component.processMouseEvent(Component.java:6527)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at java.awt.Component.processEvent(Component.java:6292)
    at java.awt.Container.processEvent(Container.java:2234)
    at java.awt.Component.dispatchEventImpl(Component.java:4883)
    at java.awt.Container.dispatchEventImpl(Container.java:2292)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4898)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4533)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4462)
    at java.awt.Container.dispatchEventImpl(Container.java:2278)
    at java.awt.Window.dispatchEventImpl(Window.java:2739)
    at java.awt.Component.dispatchEvent(Component.java:4705)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:746)
    at java.awt.EventQueue.access$400(EventQueue.java:97)
    at java.awt.EventQueue$3.run(EventQueue.java:697)
    at java.awt.EventQueue$3.run(EventQueue.java:691)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:86)
    at java.awt.EventQueue$4.run(EventQueue.java:719)
    at java.awt.EventQueue$4.run(EventQueue.java:717)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:75)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:716)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:201)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:116)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:101)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:93)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:82)

在这里，我已将触发器附加到此过程:

CREATE TRIGGER achatTrigger AFTER INSERT OR DELETE on entreedetaille
FOR EACH ROW EXECUTE PROCEDURE updateqteinstock();



CREATE TRIGGER venteTrigger AFTER INSERT OR DELETE on sortiedetaille
FOR EACH ROW EXECUTE PROCEDURE updateqteinstock();

最佳答案

将 select 值设置为 qteInitiale 变量时，您需要 start (和 end)。

像这样改变:

qteInitiale := (select qteinitiale from produit where produitid = produitID) ;

关于java - 创建 PostgreSql 触发器来更新库存数量，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/27963042/

26 4 0
文章推荐： java - Lucene中的"createComponents"有时只被调用，为什么？
文章推荐： java - 在 Java 中使用 boolean 值
文章推荐： java - 错误: "org.apache.axis2.AxisFault: Transport error: 403 Error: Forbidden"
文章推荐： java - Maven 配置将项目版本放在 war 文件内的文件名上以使用 'unzip -l app.war' 读取
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com