By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,269 Members | 1,332 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,269 IT Pros & Developers. It's quick & easy.

Trigger to delete record from one table with check on another table.

P: n/a
Hi. I want to create a trigger that only allows delete from table A if
corresponding record in table B does not exist. Any idea on how this
can be done? Thanks, Kenneth.
Jul 19 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Is there a column in table B that references the primary key of table A?

If so, won't the constraint prevent the deletes you want to prevent?
Jul 19 '05 #2

P: n/a
Don't you wanna use referential integrity constraints to achieve this?

ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce*************************@posting.google.c om>...
Hi. I want to create a trigger that only allows delete from table A if
corresponding record in table B does not exist. Any idea on how this
can be done? Thanks, Kenneth.

Jul 19 '05 #3

P: n/a
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce*************************@posting.google.c om>...
Hi. I want to create a trigger that only allows delete from table A if
corresponding record in table B does not exist. Any idea on how this
can be done? Thanks, Kenneth.


Sounds more like you want a reference constraint.
Do you have Primary keys and Foreign Keys set up on the corredsponding
tables? Looks like from the description table A is the parent table
(wiht a primary key) and table B is the child table (with a foreign
key that references the primary key of table A)

HTH,
ed
Jul 19 '05 #4

P: n/a
ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce*************************@posting.google.c om>...
Hi. I want to create a trigger that only allows delete from table A if
corresponding record in table B does not exist. Any idea on how this
can be done? Thanks, Kenneth.


Sounds more like you want a reference constraint.
Do you have Primary keys and Foreign Keys set up on the corredsponding
tables? Looks like from the description table A is the parent table
(wiht a primary key) and table B is the child table (with a foreign
key that references the primary key of table A)

HTH,
ed


Hi, and thanks for your reply.

The thing is that it is not possible to use constraints based on PK's
and FK's in this database (the relations are placed on the applicaton
layer - hence it doesn't exist any PK's or FK's).

So: I figure trigger is the only solution to ensure the above
scenario. Any ideas on how the trigger would look?

Thanks,
Kenneth
Jul 19 '05 #5

P: n/a
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce**************************@posting.google. com>...
ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce*************************@posting.google.c om>...
Hi. I want to create a trigger that only allows delete from table A if
corresponding record in table B does not exist. Any idea on how this
can be done? Thanks, Kenneth.


Sounds more like you want a reference constraint.
Do you have Primary keys and Foreign Keys set up on the corredsponding
tables? Looks like from the description table A is the parent table
(wiht a primary key) and table B is the child table (with a foreign
key that references the primary key of table A)

HTH,
ed


Hi, and thanks for your reply.

The thing is that it is not possible to use constraints based on PK's
and FK's in this database (the relations are placed on the applicaton
layer - hence it doesn't exist any PK's or FK's).

So: I figure trigger is the only solution to ensure the above
scenario. Any ideas on how the trigger would look?

Thanks,
Kenneth


I cannot think of any way for a trigger to STOP a delete, other than
maybe throwing an exception?

What do you mean the relations are in the application layer?
(CAUTION: That's the path to hell!)

Do you have two tables in the database or not?

It looks like you are using a screwdriver as a hammer. It doesn't work
well and in the end both the nail and the screwdriver are in bad
shape.

Sorry I cannot be more help, but I have a genetic disorder that
debilitates my programming skills when faced with an absurd
assignment. 8^)

Good luck! you are going to need it.
Ed
Jul 19 '05 #6

P: n/a
ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce**************************@posting.google. com>...
ed********@magicinterface.com (Ed prochak) wrote in message news:<4b**************************@posting.google. com>...
ke***************@telenor.com (Kenneth Osenbroch) wrote in message news:<ce*************************@posting.google.c om>...
> Hi. I want to create a trigger that only allows delete from table A if
> corresponding record in table B does not exist. Any idea on how this
> can be done? Thanks, Kenneth.

Sounds more like you want a reference constraint.
Do you have Primary keys and Foreign Keys set up on the corredsponding
tables? Looks like from the description table A is the parent table
(wiht a primary key) and table B is the child table (with a foreign
key that references the primary key of table A)

HTH,
ed


Hi, and thanks for your reply.

The thing is that it is not possible to use constraints based on PK's
and FK's in this database (the relations are placed on the applicaton
layer - hence it doesn't exist any PK's or FK's).

So: I figure trigger is the only solution to ensure the above
scenario. Any ideas on how the trigger would look?

Thanks,
Kenneth


I cannot think of any way for a trigger to STOP a delete, other than
maybe throwing an exception?

What do you mean the relations are in the application layer?
(CAUTION: That's the path to hell!)

Do you have two tables in the database or not?

It looks like you are using a screwdriver as a hammer. It doesn't work
well and in the end both the nail and the screwdriver are in bad
shape.

Sorry I cannot be more help, but I have a genetic disorder that
debilitates my programming skills when faced with an absurd
assignment. 8^)

Good luck! you are going to need it.
Ed


:o)

I know! Consider the case closed.

Cheers,
Kenneth
Jul 19 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.