sqlxmloracle-databasetriggers

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