Oracle SQL触发器在插入/更新时计算2个值并更新第三个值

debugcn 发表于 Dev

阿提拉

我想创建一个触发器，该触发器将通过计算2个值来更新行并更新第三个值：

CREATE TABLE Customers (
    Cust_ID INTEGER PRIMARY KEY,
    Dates TIMESTAMP WITH LOCAL TIME ZONE,
    Quantity dec(7,2) NOT NULL,
    Price_per_item dec(7,2) NOT NULL,
    Total_price dec(7,2)
);

我已经做到了：

CREATE OR REPLACE TRIGGER cust_after_insert AFTER INSERT 
ON Customers
FOR EACH ROW
BEGIN
    UPDATE Customers
    SET 
    DATES = CURRENT_TIMESTAMP,
    Total_price = Quantity * Price_per_item;
END;

我遇到一些错误，尝试同时查看和编辑表。

我也尝试过这个：

CREATE SEQUENCE cust_Seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER cust_trig BEFORE INSERT ON Customers
FOR EACH ROW
BEGIN
  SELECT cust_Seq.nextval INTO :new.Fuel_ID FROM dual;
  SELECT CURRENT_TIMESTAMP INTO :new.DATES FROM dual;
  SELECT Quantity * Price_per_item INTO :new.Total_price FROM dual;
END;
/

我更喜欢第二种选择，如果还要进行更新，我将需要一些东西。

穆雷尼克

第二种方法是朝着正确的方向发展，但它需要进行一些调整以处理Total_price计算并也处理updates：

CREATE SEQUENCE cust_Seq START WITH 1 INCREMENT BY 1;
CREATE OR REPLACE TRIGGER cust_trig BEFORE INSERT OR UPDATE ON Customers
FOR EACH ROW
BEGIN
  IF INSERTING THEN
    SELECT cust_Seq.nextval INTO :new.Fuel_ID FROM dual;
    SELECT CURRENT_TIMESTAMP INTO :new.DATES FROM dual;
  END IF;
  :new.Total_price :=  :new.Quantity * :new.Price_per_item;
END;
/

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。