check if id is present and delete row? newbie question

[Delphiwhat]

Guys another quick one. Being a none Oracle person I'm getting stuck on the basics.....

1 problem....

User details are inserted into a Table called 'users'.
A table called 'Users_stats' holds other information about the client.

When a row is inserted into the 'users' table I need to check if the users.user_id is present in the 'users_stats' table. If it is present I need to delete the row in the users_stats table otherwise do nothing.


I know it sounds probably simple but as a newbie I'm making slow progress and going around in circles.

Thanks in advance

steve

 

[SantaMufasa]

Delphi,

First, is there a business reason that you should have USER_STATS for USERS that don't exist?

It sounds as though the USER_STATS table is a child of your USERS table. If that is true, then how did the child table end up with orphaned rows? I infer that you do not have a FOREIGN KEY in place on the USER_STATS table. There is a capability in Oracle to set the option "ON DELETE CASCADE" for a child table, which means if the parent record goes away, so do the child rows that are tied to the parent row.

You can clean up your existing orphans on your USER_STATS table by saying:

delete user_stats
where not exists (Select 'yada' from users
                   where users.user_id = user_stats.user_id);

Then you can set things up to ensure that you will never have orphaned USER_STATS rows again by saying:

ALTER TABLE user_stats
    ADD CONSTRAINT user_stats_user_id_fk
        FOREIGN KEY (user_ID)
        REFERENCES users (user_id)
        ON DELETE CASCADE;

This should set everything straight for you. If you can give me proof of a valid business need to orphan the children, I can show you a way to implement the scenario you described in your initial post, but you need to have a darn good justification.

Let us know what you decide.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[Delphiwhat]

Hi Mufasa

The two tables are totally independent. No relationships.
The original text is what I need a trigger to do,

Upon insertion into tableA .....

check if tableA.user_id exists in tableB.userid and if an entry is found delete the row found in tableB

I know it sounds strange but thats apparently the way the powers that be want it. Thanks for your post

) steve

 

[BJCooperIT]

Dave may have a more elegant solution, but how about in an INSERT trigger on the "users" table:

delete from users_stats us
where  us.user_id = :new.user_id;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[SantaMufasa]

Sorry Delphi, A business rule of "...that's apparently the way the powers that be want it," absolutely, positively does not cut the mustard. You do absolutely, positively have a relationship between USERS and USER_TASKS (with the USER_ID in both tables) whether "The Powers That Be" want to admit it or not. The fact that they want all USER_TASKS for a particular user to disappear when a new USERS row arrives on the scene tells me that they don't really want orphaned children.

For me (or other reputable Oracle Helpers) to feel comfortable offering the solution which you request, you must disclose the type of risks that the company will suffer if USERS_TASKS (children) disappear when the associated USER (parent) disappears.

Additionally, unless "The Powers That Be" are your Technical Review Committee and can show Business Cause for orphaning children, then non-technical "Powers That Be" absolutely, positively should not be giving you advice on how to build your system. That's similar to me telling my trusted auto mechanic how to repair my car.

Let me know your thoughts,

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.