Oracle PL/SQL – After DELETE Trigger example

摘要: This article shows you how to use AFTER DELETE TRIGGER, it will fire after the delete operation is executed. In real life scenarios, it is mostly used for purposes like:

This article shows you how to use AFTER DELETE TRIGGER, it will fire after the delete operation is executed. In real life scenarios, it is mostly used for purposes like:

  1. Auditing or logging

1. After DELETE Trigger

In this example, if the user deleted a row of medical_bills, the deleted row will be inserted into medical_bills_history by after delete trigger on table medical_bills

1.1 Create tables and trigger.

medical_bills
CREATE TABLE medical_bills
    BILL_ID number(10) primary key,
    BILL_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    BILL_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);
medical_bills_history
CREATE TABLE medical_bills_history
    BILL_ID number(10),
    BILL_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    BILL_DATE date,
    DELETED_BY varchar2(20),
    DELETED_DATE date
);
trg_after_delete_bill
CREATE OR REPLACE TRIGGER trg_after_delete_bill
AFTER DELETE
  ON medical_bills
  FOR EACH ROW
DECLARE
username varchar2(10);
BEGIN
  -- current login user, in this example, system
  SELECT user INTO username FROM dual;
  -- Insert OLD values in history table with username of user performing delete and sysdate as deleted_Date.
  INSERT INTO medical_bills_history VALUES(:OLD.BILL_ID,
           :OLD.BILL_NUMBER,:OLD.PARTY_NAME,:OLD.BILL_DATE,username,sysdate);
END;

1.2 Insert data for testing.

INSERT INTO MEDICAL_BILLS values (1,'BILL101','Peter Thomas','12-MAY-2016','HR',sysdate);
INSERT INTO MEDICAL_BILLS values (2,'BILL102','Jemes Petil','10-JUN-2016','HR',sysdate);
INSERT INTO MEDICAL_BILLS values (3,'BILL103','Fujit su','10-JUL-2015','HR',sysdate);

1.3 Display the data.

select * from MEDICAL_BILLS;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
1 BILL101 Peter Thomas 12-MAY-2016 HR 18-JUN-2017
2 BILL102 Jemes Petil 10-JUN-2016 HR 18-JUN-2017
3 BILL103 Fujit su 10-JUL-2015 HR 18-JUN-2017
select * from MEDICAL_BILLS_HISTORY;
-- no result

1.4 Delete some rows from table MEDICAL_BILLS

DELETE FROM MEDICAL_BILLS WHERE bill_id = 1;
DELETE FROM MEDICAL_BILLS WHERE bill_id = 3;

1.5 Display the data again.

select * from MEDICAL_BILLS;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
2 BILL102 Jemes Petil 10-JUN-2016 HR 18-JUN-2017
select * from MEDICAL_BILLS_HISTORY;
BILL_ID BILL_NUMBER PARTY_NAME BILL_DATE CREATED_BY CREATED_DATE
1 BILL101 Peter Thomas 12-MAY-2016 SYSTEM 18-JUN-2017
3 BILL103 Fujit su 10-JUL-2015 SYSTEM 18-JUN-2017

References

  1. Creating a DML Trigger: Examples
  2. PL/SQL Triggers :- Oracle official docs
  3. Oracle PL/SQL – Before DELETE Trigger example

上一篇: Spring @ExceptionHandler and RedirectAttributes
下一篇: Spring Boot Configure maxSwallowSize in embedded Tomcat
 评论 ( What Do You Think )
名称
邮箱
网址
评论
验证
   
 

 


  • 微信公众号

  • 我的微信

站点声明:

1、一号门博客CMS,由Python, MySQL, Nginx, Wsgi 强力驱动

2、部分文章来源于互联网, 若有侵权, 联系邮箱:summer@yihaomen.com, 同时欢迎大家注册用户,主动发布文章.

3、鄂ICP备14001754号-3, 鄂公网安备 42280202422812号