Oracle PL / SQL –删除触发器之前的示例

本文向您展示如何使用BEFORE DELETE TRIGGER ，它将在执行删除操作之前启动。 在现实生活中，它主要用于以下目的：

1. 限制无效的DELETE操作。
2. 从另一个表中删除数据。

1.限制无效的DELETE操作

在此示例中，我们有两个表item_details和order_details 。 表order_details包含来自item_details表的项目的采购订单的值。 现在，无论何时用户想要从item_details中删除项目，我们都需要检查该项目是否存在任何挂单。

如果找到任何待处理订单，则我们将不允许删除该项目，并且会BEFORE DELETE TRIGGER引发应用程序错误，以限制对item_details删除操作

1.1创建表并触发。

ITEM_DETAILS

CREATE TABLE ITEM_DETAILS
(
	ITEM_ID number(10) primary key,
	ITEM_NAME varchar2(30),
	TYPE varchar2(50),
	PRICE_IN_DOLLAR number(10)
);

ORDER_DETAILS

CREATE TABLE ORDER_DETAILS
(
	ORDER_ID number(10) primary key,
	ITEM_ID number(10),
	QUANTITY number(5),
	ORDER_DATE date,
	STATUS varchar2(20)
);

trg_before_item_delete

CREATE OR REPLACE TRIGGER trg_before_item_delete
BEFORE DELETE
  on item_details
  FOR EACH ROW 

DECLARE
pending_orders number;

BEGIN
pending_orders := 0;
-- Find pending orders
SELECT count(1) INTO pending_orders FROM order_Details WHERE item_id = :OLD.item_id AND STATUS = 'PENDING';

   -- Check whether any pending order exists or not
    IF (pending_orders > 0) THEN
      RAISE_APPLICATION_ERROR(-20000,pending_orders||
        ' pending orders found for this item. First COMPLETE or CANCEL the order and then delete.');
    END IF;
    
END;

1.2插入数据进行测试。

INSERT INTO ITEM_DETAILS VALUES (1,'Fidget Spinner','TOYS',10);
INSERT INTO ITEM_DETAILS VALUES (2,'Radio','ELECTRONICS',15);
INSERT INTO ITEM_DETAILS VALUES (3,'Toys Car','TOYS',25);
INSERT INTO ITEM_DETAILS VALUES (4,'Mobile','ELECTRONICS',150);

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO ORDER_DETAILS VALUES (101,1,5,'10-JUN-2017','COMPLETED');
INSERT INTO ORDER_DETAILS VALUES (102,2,2,'15-JUN-2017','CANCELLED');
INSERT INTO ORDER_DETAILS VALUES (103,4,1,'17-JUN-2017','PENDING');
INSERT INTO ORDER_DETAILS VALUES (104,4,1,'01-JUN-2017','COMPLETED');

1.3显示数据。

select * from ITEM_DETAILS;

ITEM_ID	项目名称	类型	PRICE_IN_DOLLAR
1个	Fidget Spinner	玩具	10
2	无线电	电子产品	15
3	玩具车	玩具	25
4	移动	电子产品	150

select * from ORDER_DETAILS;

ORDER_ID	ITEM_ID	数量	订购日期	状态
101	1个	5	2017年6月10日	已完成
102	2	2	2017年6月15日	取消
103	4	1个	2017年6月17日	待处理
104	4	1个	2017年6月1日	已完成

1.4删除有挂单的项目。

DELETE FROM item_details WHERE item_id = 4;

-- output
Error report -
ORA-20000: 1 pending orders found for this item. First COMPLETE or CANCEL the order and then delete.
ORA-06512: at "SYSTEM.TRG_BEFORE_ITEM_DELETE", line 11
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_ITEM_DELETE'

2.从另一个表中删除

在这个例子中，我们有两个表patient和patient_details 。 该patient包含基本的细节，而patient_details包含如疾病，医生姓名等病人的价值观

现在，每当用户希望从删除数据patient ，我们需要从删除数据patient_details ，也因为我们不需要任何更多的患者被删除后。 因此，在这里我们将通过“删除BEFORE DELETE TRIGGER功能BEFORE DELETE TRIGGER患者表上的数据。

2.1创建表并触发。

PATIENT

CREATE TABLE PATIENT
(
	PATIENT_ID number(10) primary key,
	NAME varchar2(30),
	PHONE_NO number(12)
);

PATIENT_DETAILS

CREATE TABLE PATIENT_DETAILS
(
	PD_ID number(10) primary key,
	PATIENT_ID number(10),
	DISEASE varchar2(50),
	ADMITTED_DATE date,
	DOCTOR varchar2(30)
);

trg_delete_from_details

CREATE OR REPLACE TRIGGER trg_delete_from_details
BEFORE DELETE
  on patient
  FOR EACH ROW 

BEGIN

-- Delete from PATIENT_DETAILS also
DELETE FROM PATIENT_DETAILS PD WHERE PD.PATIENT_ID = :OLD.PATIENT_ID;
    
END;

2.2插入数据进行测试。

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO PATIENT VALUES(1,'Devil Lal',9898989898);
INSERT INTO PATIENT VALUES(2,'Martin Kiyosaki',9090909090);

INSERT INTO PATIENT_DETAILS VALUES(101,1,'FEVER','10-JUN-2016','Dr. RJ Sharma');
INSERT INTO PATIENT_DETAILS VALUES(102,1,'COLD','01-DEC-2016','Dr. RJ Sharma');
INSERT INTO PATIENT_DETAILS VALUES(103,2,'ARTHRITIS','01-DEC-2015','Dr. KD Verma');
INSERT INTO PATIENT_DETAILS VALUES(104,2,'BACKPAIN','12-FEB-2017','Dr. KD Verma');

2.3显示数据。

select * from PATIENT;

PATIENT_ID	名称	电话号码
1个	魔鬼拉尔	9898989898
2	马丁清崎	9090909090

select * from PATIENT_DETAILS;

PD_ID	PATIENT_ID	疾病	ADMITTED_DATE	医生
101	1个	发热	2016年6月10日	RJ Sharma博士
102	1个	冷	2016年12月1日	RJ Sharma博士
103	2	关节炎	2015年12月1日	KD Verma博士
104	2	背疼	2017年2月12日	KD Verma博士

2.4从patient表中删除项目。 查看patient_detail表，相关数据将被触发器自动删除。

DELETE FROM patient WHERE patient_id = 2;

-- output
-- 1 row deleted.

select * from PATIENT;

PATIENT_ID	名称	电话号码
1个	魔鬼拉尔	9898989898

select * from PATIENT_DETAILS;

PD_ID	PATIENT_ID	疾病	ADMITTED_DATE	医生
101	1个	发热	2016年6月10日	RJ Sharma博士
102	1个	冷	2016年12月1日	RJ Sharma博士

参考文献

1. 创建DML触发器：示例
2. PL / SQL触发器：-Oracle官方文档
3. Oracle PL / SQL – UPDATE触发器之前的示例

标签： 删除 oracle plsql 触发器 之前

翻译自: https://mkyong.com/oracle/oracle-plsql-before-delete-trigger-example/