Search code examples
javamysqljdbcsql-deletecascading-deletes

How to count rows affected by cascade

Let's say I have two tables A and B. Table B has a foreign key that references Table A's primary key. And the foreign key has 'on delete cascade' constraint.

And I am also using Java and JDBC to access this database.

How do I know the number of rows of Table B affected by delete cascade constraint? I have to get the number in Java Application. Does connection object or something has any parameter or method that returns it?

Solution

  • To follow after DELETE FROM statement you can use an audit table and BEFORE DELETE trigger.

    // change type of deleted_id to your primary key's type.
create table deleted_audit ( deleted_id BIGINT, dt TIMESTAMP, count INT );

DELIMITER $$

CREATE TRIGGER count_deleted BEFORE delete ON a 
FOR EACH ROW 
BEGIN
   DECLARE count_items INT;
   SELECT count(*) FROM b WHERE a_id=OLD.id INTO count_items;
   INSERT INTO deleted_audit VALUES( OLD.id, NOW(), count_items);
END; $$

DELIMITER ;