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.
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> |