创建postgresql触发器来更新库存数量

iyfjxgzm  于 2021-06-30  发布在  Java
关注(0)|答案(1)|浏览(363)

我想为使用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 = ?

触发器需要在EntereDetaille或sortiedetaille表上发生insert事件后调用。
我已经开始尝试，但不适合我：

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();
Javapostgresqltriggersplpgsql

1条答案

按热度按时间
sigwle7e

sigwle7e1#

将select value设置为QTEINITALE变量时需要开始（和结束）。
变化如下：

qteInitiale := (select qteinitiale from produit where produitid = produitID) ;
赞(0）分享  回复(0）举报  2021-06-30
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com