如何编写PL/SQL Trigger实现多表插入？脚本报ORA-00917错误

问题背景 #

从远程数据库同步订单数据至C3_Order_Headers表后，需通过PL/SQL触发器将订单信息拆分插入到Orders、Customers、Products、Order_Products四张关联表中。使用生成的触发器脚本时触发ORA-00917（缺失逗号）错误，需排查问题并实现预期数据插入逻辑。

订单数据样例 #

{"order_number": "58a01c99-ac60-4f4f-b05e-781714b797aa","order_date": "1/21/2022","first_name": "Vinni","last_name": "Candey","email": "vcandey5@de.vu","ip_address": "249.33.234.247","credit_card": "372301965255898","currency_code": "USD","city": "Milwaukee","street": "28 Kim Point","state": "Wisconsin","postal_code": "53210","log_data": "👩🏽","lines": [{"product": "Tomatoes - Roma","quantity": 1,"price": "$89.21","item_image": null},{"product": "Coffee Swiss Choc Almond","quantity": 6,"price": "$162.09","item_image": null}]}

原报错触发器脚本 #

CREATE OR REPLACE TRIGGER insert_order_data
AFTER INSERT ON orders_data
FOR EACH ROW
DECLARE
  customer_id NUMBER;
BEGIN
  -- Insert customer data into Customers table
  INSERT INTO Customers (first_name, last_name, email, ip_address, credit_card, city, street, state, postal_code)
  VALUES (:new.first_name, :new.last_name, :new.email, :new.ip_address, :new.credit_card, :new.city, :new.street, :new.state, :new.postal_code)
  RETURNING id INTO customer_id;

  -- Insert order data into Orders table
  INSERT INTO Orders (order_number, order_date, customer_id, currency_code)
  VALUES (:new.order_number, TO_DATE(:new.order_date, 'MM/DD/YYYY'), customer_id, :new.currency_code);

  -- Insert product data into Products table and get product_ids
  DECLARE
    product_id NUMBER;
  BEGIN
    FOR i IN 1..JSON_ARRAYSIZE(:new.lines)
    LOOP
      INSERT INTO Products (name, price, image)
      VALUES (JSON_VALUE(:new.lines[i], '$.product'), SUBSTR(JSON_VALUE(:new.lines[i], '$.price'), 2), JSON_VALUE(:new.lines[i], '$.item_image'))
      RETURNING id INTO product_id;

      -- Insert order product data into Order_Products table
      INSERT INTO Order_Products (order_number, product_id, quantity)
      VALUES (:new.order_number, product_id, JSON_VALUE(:new.lines[i], '$.quantity'));
    END LOOP;
  END;
END;
/

报错信息 #

Error at line 18: PL/SQL: ORA-00917: missing comma
Error at line 21: PL/SQL: SQL Statement ignored
Error at line 22: PL/SQL: ORA-00917: missing comma
Error at line 17: PL/SQL: SQL Statement ignored


1. CREATE OR REPLACE TRIGGER insert_order_data
2. AFTER INSERT ON C3_order_headers
3. FOR EACH ROW

预期数据插入效果 #

• Customers表：自动生成id，插入姓名、邮箱、地址等客户信息
• Orders表：关联刚插入的客户id，插入订单编号、日期、货币代码
• Products表：自动生成id，插入商品名称、价格、图片信息（每条订单行对应一条商品记录）
• Order_Products表：关联订单编号与商品id，插入购买数量

具体数据示例：

Customers 
 id: (自动生成)
 first_name: "Vinni",
 last_name: "Candey",
 email: "vcandey5@de.vu",
 ip_address: "249.33.234.247",
 credit_card: "372301965255898",
 city: "Milwaukee",
 street: "28 Kim Point",
 state: "Wisconsin",
 postal_code: "53210",
    
    
 Orders:
 order_number:"58a01c99-ac60-4f4f-b05e-781714b797aa",
 order_date: "1/21/2022",
 customer_id: (关联Customers表的id)
 currency_code: "USD"

Products 
(
  id: (自动生成)
  name: "Tomatoes - Roma",
  price: "$89.21",
  image: null
)
(
  id: (自动生成)
  name: "Coffee Swiss Choc Almond",
  price: "$162.09",
  image: null
)

Order_Products:
(
order_number:"58a01c99-ac60-4f4f-b05e-781714b797aa",
product_Id: (关联Products表的id),
quantity: 1
)
(
order_number:"58a01c99-ac60-4f4f-b05e-781714b797aa",
product_Id: (关联Products表的id),
quantity: 6
)

问题排查与修复 #

错误根源 #

1. 触发表不匹配：原脚本中触发器监听orders_data表，但实际数据同步到C3_order_headers表，导致逻辑错位。
2. JSON数组遍历语法错误：Oracle中无法直接用:new.lines[i]访问JSON数组元素，JSON_ARRAYSIZE的使用方式不符合Oracle JSON处理规范，导致解析时被判定为缺失逗号。

修复后的触发器脚本 #

CREATE OR REPLACE TRIGGER insert_order_data
AFTER INSERT ON C3_order_headers
FOR EACH ROW
DECLARE
  v_customer_id NUMBER;
  v_product_id NUMBER;
  -- 定义游标遍历订单行JSON数据
  CURSOR c_order_lines IS
    SELECT 
      JSON_VALUE(line, '$.product') AS product_name,
      JSON_VALUE(line, '$.price') AS product_price,
      JSON_VALUE(line, '$.item_image') AS product_image,
      JSON_VALUE(line, '$.quantity') AS product_quantity
    FROM JSON_TABLE(:new.lines, '$[*]' COLUMNS line CLOB PATH '$');
BEGIN
  -- 插入客户数据并获取自动生成的ID
  INSERT INTO Customers (first_name, last_name, email, ip_address, credit_card, city, street, state, postal_code)
  VALUES (:new.first_name, :new.last_name, :new.email, :new.ip_address, :new.credit_card, :new.city, :new.street, :new.state, :new.postal_code)
  RETURNING id INTO v_customer_id;

  -- 插入订单数据，关联客户ID
  INSERT INTO Orders (order_number, order_date, customer_id, currency_code)
  VALUES (:new.order_number, TO_DATE(:new.order_date, 'MM/DD/YYYY'), v_customer_id, :new.currency_code);

  -- 遍历订单行，插入商品及关联关系
  FOR rec IN c_order_lines LOOP
    -- 插入商品数据并获取ID
    INSERT INTO Products (name, price, image)
    VALUES (rec.product_name, rec.product_price, rec.product_image)
    RETURNING id INTO v_product_id;

    -- 插入订单-商品关联数据
    INSERT INTO Order_Products (order_number, product_id, quantity)
    VALUES (:new.order_number, v_product_id, rec.product_quantity);
  END LOOP;
END;
/

修复说明 #

1. 修正触发器监听的表为C3_order_headers，匹配实际数据同步目标表。
2. 使用JSON_TABLE将JSON数组转换为关系型数据，通过游标遍历订单行，避免原生数组索引的语法错误。
3. 保留了客户ID、商品ID的返回逻辑，确保表之间的关联关系正确。
4. 商品价格直接保留原始格式（带$符号），符合预期插入效果。

内容的提问来源于stack exchange，提问作者Kurtonio