I'd like to use a delete-trigger to do "something" when the "last row" of a particular piece of data is deleted. Below is the example I put together... In this example, it is when the last family member of a family is deleted.
A simple table called MYNAME with a field for the FIRST and LAST name.
Upon any delete... If the record being deleted was the only/last record in the table with that particular last name... then do "something".
I tried doing this via an "AFTER" delete-trigger and doing a count on the table for the last name that was deleted... and if it was zero, i would do "something"... But I get the mutating error. I think I understand why I get this error, but I'm not sure how I can do what I want with a delete-trigger.
Below is the code to duplicate. The delete statement at the end will cause a mutating error on the trigger the way it is now.
SET ECHO OFF
set serveroutput on
SET DEFINE OFF
CREATE TABLE MYNAMES (
FIRST VARCHAR2(20) NOT NULL , LAST VARCHAR2(20) NOT NULL , CONSTRAINT MYNAMES_PK PRIMARY KEY ( FIRST , LAST ) ENABLE
);
drop trigger TR_MYNAMES; -- Trigger may not exist yet.
delete from MYNAMES;
INSERT INTO MYNAMES(FIRST, LAST) VALUES ('John', 'Doe');
INSERT INTO MYNAMES(FIRST, LAST) VALUES ('Jane', 'Doe');
INSERT INTO MYNAMES(FIRST, LAST) VALUES ('Bob', 'Smith');
INSERT INTO MYNAMES(FIRST, LAST) VALUES ('Mary', 'Smith');
commit;
create or replace
trigger TR_MYNAMES
AFTER DELETE ON MYNAMES
FOR EACH ROW
DECLARE
MYCOUNT number;
BEGIN
SELECT COUNT(*) INTO MYCOUNT FROM MYNAMES M WHERE M."LAST" = :old."LAST";
if (MYCOUNT = 0) then
null; --If last family member was deleted, then do something here.
end if;
END;
/
delete from mynames where first = 'Mary' and last = 'Smith'
;
commit;