sql - 如何在 Oracle Express 中创建触发器或过程以自动执行表列中的值

问题描述

如何line_total在帐户 ( ) 的列中输入值sales_line.line_total = product.unit_price * sales_line.line_qty？

我想有一个触发器或一个程序来自动化这个。当我在列中输入值时，它会自动触发触发器，进行计算并将结果插入列中sales_line.line_total。

我还想自动化列sale.sale_total，这将是 values 列的总和sales_line.line_total。你能为这两个问题制定一个触发器或程序吗？

你能帮助我吗？

CREATE TABLE product (
    product_id    NUMBER(4) NOT NULL,
    category_id   NUMBER(4) NOT NULL,
    p_desc        VARCHAR2(40),
    cpu           VARCHAR2(14),
    ram           VARCHAR2(14),
    capacity      VARCHAR2(14),
    screen_size   VARCHAR2(14),
    battery       VARCHAR2(14),
    unit_price    NUMBER(7, 2),
    colour        VARCHAR2(14),
    qty_stock     NUMBER(4)
);
ALTER TABLE product ADD CONSTRAINT product_pk PRIMARY KEY ( product_id );

CREATE TABLE sale (
    sale_id       NUMBER(4) NOT NULL,
    sale_date     DATE,
    customer_id   NUMBER(4) NOT NULL,
    employee_id   NUMBER(4) NOT NULL,
    sale_total    NUMBER(7, 2)
);

ALTER TABLE sale ADD CONSTRAINT sale_pk PRIMARY KEY ( sale_id );

CREATE TABLE sales_line (
    sale_id      NUMBER(4) NOT NULL,
    product_id   NUMBER(4) NOT NULL,
    line_qty     NUMBER(4),
    line_total   NUMBER(7, 2)
);

ALTER TABLE sales_line ADD CONSTRAINT index_3 PRIMARY KEY ( sale_id,
                                                            product_id );

ALTER TABLE product
    ADD CONSTRAINT product_p_category_fk FOREIGN KEY ( category_id )
        REFERENCES p_category ( category_id );

ALTER TABLE sale
    ADD CONSTRAINT sale_customer_fk FOREIGN KEY ( customer_id )
        REFERENCES customer ( customer_id );

ALTER TABLE sale
    ADD CONSTRAINT sale_employee_id_fk FOREIGN KEY ( employee_id )
        REFERENCES employee ( employee_id );

ALTER TABLE sales_line
    ADD CONSTRAINT sales_line_product_fk FOREIGN KEY ( product_id )
        REFERENCES product ( product_id );

ALTER TABLE sales_line
    ADD CONSTRAINT sales_line_sale_fk FOREIGN KEY ( sale_id )
        REFERENCES sale ( sale_id );

以下是我迄今为止编写的触发器：

CREATE OR REPLACE TRIGGER trg_line_total_ai AFTER INSERT OR UPDATE ON sales_line
FOR EACH ROW
DECLARE
    lt_value NUMBER(7,2);
BEGIN
    SELECT product.unit_price INTO lt_value FROM product;
    UPDATE sales_line
    SET line_total = :NEW.line_qty * lt_value
   -- SET line_total = (line_qty * :NEW.unit_price)   
    WHERE product_id = :NEW.product_id;

    --UPDATE sales_line
      --  SET line_total = (line_qty * :OLD.unit_price)
        --WHERE product.product_id = :OLD.product_id;
END;


CREATE OR REPLACE TRIGGER trg_sale_total_ai AFTER INSERT OR UPDATE ON sales_line
FOR EACH ROW
BEGIN     
    UPDATE sale     
    SET sale_total = (sale_total + :NEW.line_total)   
    WHERE sale_id = :NEW.sale_id;

    UPDATE sale
        SET sale_total = (sale_total + :OLD.line_total)
        WHERE sale_id = :OLD.sale_id;
END;

标签： sqloracledatabase-trigger

sql - 如何在 Oracle Express 中创建触发器或过程以自动执行表列中的值

问题描述

解决方案

推荐阅读