在客户下订单的地方触发删除 [英] Trigger on delete where customers have had an order

问题描述

我正在为删除的客户创建以前的客户表. 我想在从客户表中删除数据后创建触发器. 但是，我不希望所有客户仅被先前已在系统中下订单的客户放置在此表中. 我有两个桌子

I am creating a previous customers table for customers who are deleted. I'm wanting to create a trigger after data has been deleted from a customer table. However i don't want all the customers to be placed in this table only those who have previously placed an order in the system. I have two tables

Customer                 placed_order
Customer_Id              order_id
first_name               order_date
last_name                employee_ID
address                  fk1_customer_id

怎么可能只在下订单表中插入有记录的客户?

How would it be possible to only insert customers who have a record in the placed orders table?

推荐答案

类似的东西

CREATE OR REPLACE TRIGGER CUSTOMER_AD
  AFTER DELETE ON CUSTOMER
  REFERENCING OLD AS OLD
  FOR EACH ROW
DECLARE
  nPlaced_order_count  NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO nPlaced_order_count
    FROM PLACED_ORDERS p
    WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID;

  IF nPlaced_order_count > 0 THEN
    INSERT INTO PREVIOUS_CUSTOMER(whatever, whatever, whatever)
      VALUES(whatever, whatever, whatever);
  END IF;

  -- Now clean up the PLACED_ORDERS table

  DELETE FROM PLACED_ORDERS p
    WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID;
END CUSTOMER_AD;

用适合于PREVIOUS_CUSTOMER表的列名和值替换whatever.

Replace whatever with column names and values appropriate for your PREVIOUS_CUSTOMER table.

分享并享受.

这篇关于在客户下订单的地方触发删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！