Oracle Trigger Creation Error: PLS-00049 BAD BIND VARIABLE 'OLD'

I have created Backup Table To keep record Of Deleted Data from any Table, a Procedure and a Trigger.

I want to store data in XML to store complete row and fetch data using XML.

I am using Oracle SQL+

But trigger is created with compilation error. Pls-00049 bad bind variable 'OLD'

I tried below code

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;


CREATE OR REPLACE TRIGGER trg_employees_backup
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
  log_deleted_data('employees', SYS_XMLGEN(:OLD).GETCLOBVAL());
END trg_employees_backup;

ERROR: PLS-00049 bad bind variable 'OLD'

Please correct me. Thank you.

Solution

You can use:

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;
/

Which, for the sample data:

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;

Then, if you delete rows:

DELETE FROM Employees WHERE id > 1;

The deleted_data_backup table contains:

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>

fiddle