|
|
Re: After delete of all rows from a table, update another table, HOW? [message #41168 is a reply to message #41164] |
Tue, 03 December 2002 11:47 |
Afagela
Messages: 9 Registered: December 2002
|
Junior Member |
|
|
Thanks for your reply. I would like to delete a row from the second table based on whether all the rows from the first table have been deleted.
(i.e. On delete of all rows in Table_1, delete the row in Table_2 where field value = 0 ). The second table will have a record. I hope this is clear. I am all new to pl/sql. Thanks.
|
|
|
Re: After delete of all rows from a table, update another table, HOW? [message #41170 is a reply to message #41164] |
Tue, 03 December 2002 12:52 |
Rick Cale
Messages: 111 Registered: February 2002
|
Senior Member |
|
|
I am assuming there is a field in table_2 that has
a column = 0 and also for this ex 2 key fields.
DELETE FROM table_2 a
WHERE column_name=0
AND a.key1||a.key2 NOT IN
(SELECT key1||key2 FROM table_1);
I suspect we are still having a confusion on
the requirements but this will delete all records
in table_2 that have specified column=0 and no corresponding record exist in table_1
On delete of all rows in Table_1, delete the row in Table_2 where field value = 0 ). The second table will have a record. I hope this is clear. I am all new to pl/sql. Thanks
|
|
|
Re: After delete of all rows from a table, update another table, HOW? [message #41172 is a reply to message #41164] |
Wed, 04 December 2002 06:53 |
Afagela
Messages: 9 Registered: December 2002
|
Junior Member |
|
|
Thank you. I guess I am not all clear with my requirements.
Table1 = Employee (JobTitle,Salary,field1)
Table2 = JobDescription (Jobtitle,Rank,fieldA)
(The field rank can be from 1 - 3 ).
1. A user (manager) opens a window application and removes all ADMIN jobtitle from the Employee menu.
2. This action by the user will result in a delete of all ADMIN jobtitle from the Employee Table (Background process).
3. From this action, a trigger should fire and delete a record in JobDescription Table where rank = 1 for the JobTitle.
4. So on delete of all ADMIN jobtitle from the Employee menu, I need to delete JobDescription with rank value of 1. Let's just say that there are 3 records in the JobDescription table for ADMIN with rank 1, 2 and 3.
Does this example make sense. I was told that this is in form of a collection or mutating table? I started writing an AFTER DELETE Trigger but I'm stuck. Will the solution you gave me work for this example. I guess I can try it and see. Thanks for your help!
|
|
|