Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE Employees (id, first_name, last_name, date_of_birth) AS
SELECT 1, 'Alice', 'Abbot', DATE '1970-01-01' FROM DUAL UNION ALL
SELECT 2, 'Betty', 'Baron', DATE '1980-01-01' FROM DUAL UNION ALL
SELECT 3, 'Carol', 'Count', DATE '1990-01-01' FROM DUAL;
3 rows affected

CREATE TABLE deleted_data_backup (
  id NUMBER GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1),
  deleted_at TIMESTAMP DEFAULT SYSTIMESTAMP,
  table_name VARCHAR2(30),
  data CLOB
);
CREATE OR REPLACE PROCEDURE log_deleted_data(
  p_table_name IN VARCHAR2,
  p_data IN SYS.XMLTYPE
) AS
BEGIN
  INSERT INTO deleted_data_backup (table_name, data)
  VALUES (p_table_name, p_data.GETCLOBVAL());
END log_deleted_data;
/
SELECT XMLELEMENT(
      "Employee",
      XMLFOREST(
        first_name    AS "FirstName",
        last_name     AS "LastName",
        date_of_birth AS "DateOfBirth",
        id
      )
    ) AS xml
FROM   Employees


XML


<Employee><FirstName>Alice</FirstName><LastName>Abbot</LastName><DateOfBirth>1970-01-01</DateOfBirth><ID>1</ID></Employee>

<Employee><FirstName>Betty</FirstName><LastName>Baron</LastName><DateOfBirth>1980-01-01</DateOfBirth><ID>2</ID></Employee>

<Employee><FirstName>Carol</FirstName><LastName>Count</LastName><DateOfBirth>1990-01-01</DateOfBirth><ID>3</ID></Employee>


CREATE OR REPLACE TRIGGER trg_employees_backup
AFTER DELETE ON employees
FOR EACH ROW
DECLARE
  v_xml XMLTYPE;
BEGIN
  SELECT XMLELEMENT(
           "Employee",
           XMLFOREST(
             :OLD.first_name    AS "FirstName",
             :OLD.last_name     AS "LastName",
             :OLD.date_of_birth AS "DateOfBirth",
             :OLD.id            AS "ID"
           )
         )
  INTO   v_xml
  FROM   DUAL;

  log_deleted_data('employees', v_xml);
END trg_employees_backup;
/
SELECT * FROM USER_ERRORS;
DELETE FROM Employees WHERE id > 1;
2 rows affected

SELECT * FROM deleted_data_backup;


ID
DELETED_AT
TABLE_NAME
DATA


1
26-SEP-24 08.30.33.194662
employees
<Employee><FirstName>Betty</FirstName><LastName>Baron</LastName><DateOfBirth>1980-01-01</DateOfBirth><ID>2</ID></Employee>

2
26-SEP-24 08.30.33.215870
employees
<Employee><FirstName>Carol</FirstName><LastName>Count</LastName><DateOfBirth>1990-01-01</DateOfBirth><ID>3</ID></Employee>

ID	DELETED_AT	TABLE_NAME	DATA
1	26-SEP-24 08.30.33.194662	employees	<Employee><FirstName>Betty</FirstName><LastName>Baron</LastName><DateOfBirth>1980-01-01</DateOfBirth><ID>2</ID></Employee>
2	26-SEP-24 08.30.33.215870	employees	<Employee><FirstName>Carol</FirstName><LastName>Count</LastName><DateOfBirth>1990-01-01</DateOfBirth><ID>3</ID></Employee>