CODEWITHSUNDEEP
sqloracle-databasetriggersconstraints
Kousalik
Kousalik

Reputation: 3137

Simulating DELETE ON CASCADE behaviour using trigger

imagine two tables described below please. There is an "relationship" between those tables (transactionnumber columns) but they cannot conform foreign key constraint because they are not unique.

CREATE TABLE COMMAND_DATA
(
  TRANSACTIONNUMBER NUMBER(19) NOT NULL,
  KEYNAME VARCHAR2(255) NOT NULL,
  DATA VARCHAR2(255)
  CONSTRAINT PK_COMMAND_DATA PRIMARY KEY (COMMANDTRANSACTIONNUMBER, KEYNAME)
);

CREATE TABLE COMMAND
(
  TRANSACTIONNUMBER NUMBER(19) NOT NULL,
  COMMANDTYPE NUMBER(10) NOT NULL,
  TRANSACTIONTIMESTAMP NUMBER(19) NOT NULL,
  SOURCEID VARCHAR2(255),
  CONSTRAINT PK_COMMAND PRIMARY KEY (TRANSACTIONNUMBER, COMMANDTYPE)
);

Now, imagine there are for example 10 rows containing transactionnumber = 1 in COMMAND table and there are 3 rows containing also transactionnumber = 1 in COMMAND_DATA table. These data is filled by batch process once per time. Now my system is processing and deleting rows one by one from COMMAND table.

What I want to achieve is, that after deleting last row with transactionnumber = 1 from COMMAND table, all rows with same transactionnumber will be deleted from table COMMAND_DATA.

So I created following trigger:

CREATE TRIGGER CLEAN_COMMAND_DATA
AFTER DELETE ON COMMAND FOR EACH ROW 

DECLARE
   pragma autonomous_transaction;
   v_count number(10);
BEGIN
   select count(*) into v_count from command where TRANSACTIONNUMBER = :old.TRANSACTIONNUMBER;

   if v_count = 0 then
      delete from COMMAND_DATA where TRANSACTIONNUMBER = :old.TRANSACTIONNUMBER;
   end if;
END;

But it doesn't work because the select statement is also counting rows which are being deleted, so there is never situation where count(*) returns zero.

How could I adjust that trigger ? Or is there better solution ? Using DELETE ON CASCADE is not possible here because I can't use FK...

Upvotes: 1

Views: 469

Answers (1)

DrabJay
DrabJay

Reputation: 3099

As I assume you have discovered, you cannot select from the same table that a row-level trigger is defined against; it causes a table mutating exception.

You have attempted to get round this using the autonomous transaction pragma which, although it remove the exception, actually just covers the mistake in your methodology. You need to move the processing to a statement level trigger. In Oracle 11g and above you could do the following:

CREATE OR REPLACE TRIGGER clean_command_data
  FOR DELETE ON command
  COMPOUND TRIGGER

  -- Table to hold identifiers of inserted/updated transactions
  g_transactionNumbers sys.odcinumberlist;

BEFORE STATEMENT 
IS
BEGIN
-- Reset the internal transactions table
 g_transactionNumbers := sys.odcinumberlist();
END BEFORE STATEMENT; 

AFTER EACH ROW
IS
BEGIN
  -- Store the inserted/updated transactions
  g_transactionNumbers.EXTEND;
  g_transactionNumbers(g_transactionNumbers.LAST) := :old.transactionNumber;
END AFTER EACH ROW;

AFTER STATEMENT
IS
  CURSOR csr_commands
  IS
    SELECT DISTINCT
           tno.column_value transactionNumber
    FROM TABLE(g_transactionNumbers) tno
         LEFT OUTER JOIN command cmd
           ON (cmd.transactionNumber = tno.column_value)
    WHERE cmd.transactionNumber IS NULL;
BEGIN
  -- Check if for any deleted transaction there exists no more commands
  FOR r_command IN csr_commands LOOP
    DELETE FROM command_data
    WHERE transactionNumber = r_command.transactionNumber;
  END LOOP;
END AFTER STATEMENT;

END;

Upvotes: 1

Related Questions