PL SQL After Delete Trigger Not Inserting Record

Question

I have a trigger written in PL/SQL that is supposed to insert the deleted record into another table.

Here is the trigger:

CREATE OR REPLACE TRIGGER keepRemoved
AFTER DELETE
ON schema.table
FOR EACH ROW
DECLARE selection_id varchar2(10) := NULL;
BEGIN
    /* Check if deleted record already exists in destination table by populating variable with that value. */
     BEGIN
       SELECT a.primary_key
       INTO selection_id
       FROM [destinationTable] a
       WHERE a.primary_key = :old.removed_key
         AND a.condition_value = true
         AND a.condition_value2 = 42;
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           selection_id := NULL;
         WHEN OTHERS THEN
           selection_id := NULL;
      END;

    /* If selection_id is null, meaning that value doesn't exist in the destination table, insert deleted record in destination table */
      IF selection_id IS NULL THEN
        INSERT INTO [destinationTable] a
           (a.primary_key,
            a.user_id,
            a.column2,
            ...
            a.columnN)
        VALUES
            (:old.removed_key,
             SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY'),
              123,
              ...
              'MoreText')
       END IF;
       EXCEPTION
         WHEN OTHERS THEN NULL;
 END;

The issue is that when a record is deleted, nothing is populated in the destinationTable.

For context, I am never going to be the user deleting records.

Answer 1

what you put here, is like Pseudocode not real code, so I can not help so much, however here is my advice:

1. do you have errors? then follow the errors be adding debug information, write some code in exception to dbms_output.put_line to check it.

2. be sure the trigger on the same table you are executing DML operations.

3. change your trigger to work with "before delete".

4. check if a.primary_key is exists in destination table if so the insertion will not executed.

Answer 2

Hi Please change a logic other way round like bellow use count instead of value and null match. also you do not need exception handling for count.

CREATE OR REPLACE TRIGGER keepRemoved
AFTER DELETE
ON schema.table
FOR EACH ROW
DECLARE 
selection_count pls_integer:=0
BEGIN
    /* Check if deleted record already exists in destination table by populating variable with that value. */
     BEGIN
       SELECT count(*)
       INTO selection_count
       FROM [destinationTable] a
       WHERE a.primary_key = :old.removed_key
         AND a.condition_value = true
         AND a.condition_value2 = 42;
       EXCEPTION     -- can remove this.
         WHEN NO_DATA_FOUND THEN
           selection_count := 0;
         WHEN OTHERS THEN
           selection_count := 1;  
      END;

    /* If selection_id is null, meaning that value doesn't exist in the destination table, insert deleted record in destination table */
      IF selection_count = 0 THEN
        INSERT INTO [destinationTable] a
           (a.primary_key,
            a.user_id,
            a.column2,
            ...
            a.columnN)
        VALUES
            (:old.removed_key,
             SYS_CONTEXT('USERENV','AUTHENTICATED_IDENTITY'),
              123,
              ...
              'MoreText')
       END IF;
       EXCEPTION
         WHEN OTHERS THEN NULL;
 END;