Why do you have the UPDATING in the trigger? Inserting should bump up the cnt and deleting should bump down, why care if EMP is updated?

Thanks

>>Why do you have the UPDATING in the trigger? Inserting should bump up the cnt and deleting should bump down, why care if EMP is updated?

To allow for EMP records changing dept_id, of course.

Doh! Tom doesnt miss a thing, does he!

How about just a view on DEPT.

CREATE VIEW DEPT AS (
SELECT D.DEPTNO, D.DNAME, D.LOC
FROM DEPT_TABLE D, EMP E
WHERE D.DEPTNO = E.DEPTNO);

Now everyone sees the view dept with only dept that have employees.

Its not about simply viewing departments that have at least 1 employee, the original question was how to not even allow such departments to be created in the first place

Mr. Tom
Can you please explain what is :new and :old which you just used in your code.


Thanks.

If there is a foreign key arrangement between emp and dept, which there should be, you will have to enter a department before you can enter an employee. If you check to see if the dept has at least one employee, how will you ever put the employee in? But assuming that this is actually true, then try the following.



create trigger dept_trigger
after insert on dept for each row
emp_cnt number;
begin
select count(*)
from emp e
into emp_cnt
where e.dept = :new.dept;

if ent_cnt = 0 then
RAISE_APPLICATION_ERROR ( -20601, 'You have an orphaned Department');
end if;
end;

For example: suppose we have n-m relationship between task and employee.

Business rule would be:
Each employee must work upon no less than 2 and no more than 4 tasks at any time.

Again business rule may be violated during the transaction but upon commit must hold true.

Tom,

I have two tables

CREATE TABLE T_PARENT
(
parent_id NUMBER(9) NOT NULL
);

ALTER TABLE T_PARENT ADD (
CONSTRAINT T_PARENT_PK PRIMARY KEY (parent_id);

CREATE TABLE T_CHILD
(
child_id NUMBER(9) NOT NULL,
parent_id NUMBER(9) NOT NULL
);

ALTER TABLE T_CHILD ADD (
CONSTRAINT T_CHILD_PK PRIMARY KEY (child_id);

ALTER TABLE T_CHILD ADD (
CONSTRAINT T_CHILD_FK FOREIGN KEY (parent_id)
REFERENCES T_PARENT (parent_id));


Now,I want to delete parent record at the same time when child record gets deleted and have no further child left to avoide childless parent record situation. My approch is

CREATE OR REPLACE TRIGGER T_CHILD_ARD
AFTER DELETE
ON T_CHILD FOR EACH ROW
BEGIN
-- Delete childless parent record.
my_Pkg.delete_parent_record (:old.parent_id);
END;

The procedure in my_pkg is

PROCEDURE delete_parent_record (p_parent_id IN NUMBER) IS
parent_record_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (parent_record_exist, -2292);
BEGIN
-- Delete childless parent record.
DELETE FROM t_parent
WHERE parent_id = p_parent_id;
EXCEPTION
WHEN parent_record_exist THEN
-- Do Not delete parent record because one or more child records exist.
NULL;
END delete_parent_record;

The above logic is working fine if there is only one child record and that gets deleted, but if there is more than one child record then I received the error 2292.

Is there any other better solution for this?

Thanks in advance

Faisal

Tom,

I have two tables

CREATE TABLE T_PARENT
(
parent_id NUMBER(9) NOT NULL
);

ALTER TABLE T_PARENT ADD (
CONSTRAINT T_PARENT_PK PRIMARY KEY (parent_id);

CREATE TABLE T_CHILD
(
child_id NUMBER(9) NOT NULL,
parent_id NUMBER(9) NOT NULL
);

ALTER TABLE T_CHILD ADD (
CONSTRAINT T_CHILD_PK PRIMARY KEY (child_id);

ALTER TABLE T_CHILD ADD (
CONSTRAINT T_CHILD_FK FOREIGN KEY (parent_id)
REFERENCES T_PARENT (parent_id));


Now,I want to delete parent record at the same time when child record gets deleted and have no further child left to avoide childless parent record situation. My approch is

CREATE OR REPLACE TRIGGER T_CHILD_ARD
AFTER DELETE
ON T_CHILD FOR EACH ROW
BEGIN
-- Delete childless parent record.
my_Pkg.delete_parent_record (:old.parent_id);
END;

The procedure in my_pkg is

PROCEDURE delete_parent_record (p_parent_id IN NUMBER) IS
parent_record_exist EXCEPTION;
PRAGMA EXCEPTION_INIT (parent_record_exist, -2292);
BEGIN
-- Delete childless parent record.
DELETE FROM t_parent
WHERE parent_id = p_parent_id;
EXCEPTION
WHEN parent_record_exist THEN
-- Do Not delete parent record because one or more child records exist.
NULL;
END delete_parent_record;

The above logic is working fine if there is only one child record and that gets deleted, but if there is more than one child record then I received the error 2292.

Is there any other better solution for this?

Thanks,

Faisal

Hi Tom,

I like your solution. However it prevents cascading delete (due to mutating table on the triggers). Is there an elegant way to handle this? (other, say, than coding a switch off option into the triggers based on a session value which says "let me by-pass the checking")

Thanks!
Bill

Hi Tom,

Sorry, could have been clearer - the problem doesn't exist, of course, in your original example as there is no cascading delete!

Here's the problem - I've modified your code only to add an ON DELETE CASCADE clause (the rest is unchanged) to the detail table as follows:

create table dept_table
(deptno number primary key,
dname varchar2(10),
empcnt number default 0,
constraint check_children
check ( empcnt > 0 )
deferrable initially deferred
)
/

create or replace view dept
as
select deptno, dname from dept_table;

create table emp
( empno int primary key,
deptno references dept_table ON DELETE CASCADE,
ename varchar2(10)
)
/

create trigger emp_trigger
after insert or update or delete on emp for each row
begin
if (inserting or updating)
then
update dept_table set empcnt = empcnt+1 where deptno = :new.deptno;
end if;
if (deleting or updating)
then
update dept_table set empcnt = empcnt-1 where deptno = :old.deptno;
end if;
end;
/

insert into dept values ( 10, '10' );

insert into emp values ( 1, 10, 'bob' );

commit;

delete from dept where deptno = 10;

This leads to:

delete from dept where deptno = 10
*
ERROR at line 1:
ORA-04091: table DEPT_TABLE is mutating, trigger/function may not see
it
ORA-06512: at "EMP_TRIGGER", line 8
ORA-04088: error during execution of trigger 'EMP_TRIGGER'

While I can of course delete the child records first I wondered if there was a nice way (other than selectively disabling triggers) to avoid the problem and therefore retain the cascade delete functionality (useful in more complex hierarchies).

Thanks for your time!
Bill

Hi Bill from Scotland,

I fear Tom won't answer this as he hates triggers.

You have

sql > delete from dept where deptno = 10;
delete from dept where deptno = 10
*
ERROR at line 1:
ORA-04091: table DP.DEPT_TABLE is mutating, trigger/function may not see it
ORA-06512: at "DP.EMP_TRIGGER", line 8
ORA-04088: error during execution of trigger 'DP.EMP_TRIGGER'


Now you could nicely :-)

sql > create trigger dept_trigger
2 instead of delete on dept for each row
3 begin
4 delete emp where deptno = :old.deptno;
5 delete dept_table where deptno = :old.deptno;
6 end;
7 /

Trigger created.

sql > delete from dept where deptno = 10;

1 row deleted.

Thanks both Tom & Sokrates! 2 techniques that I'll experiment with, and see which suits us best. Much appreciated!

Hi Tom,

In your response to the original question you stated

... here is one approach - there are others (using on commit refresh materialized views for example) ...

Could you point me to an example of using on commit refresh materialized views for this type of problem?

Also, you alluded to other possible approaches for solving this, could you advise what they are please?

Thanks,
John.