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?
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 ;
