Help with a special stored procedure

Hi

I have a table there have a primary key, this value have i also in another
table as a forreignkey, so good so far.

Here it is
If the number in may primarykey in table 1 , dosn't exits in table 2 then
delete records in table 1

I have made this in a ASP page with a view there list all records where the forreignkey in table 2 are NULL and then delte all records in table one

Can i made this as a stored procedure ?

regards

alvin

You really want a trigger on table 1 to check table 2 before insert.

Research INSTEAD OF Insert Triggers.

What this will do is instead of inserting a new record into table 1, it will check table 2 for a match. If there is no match, no record is inserted into table 1.

It's better to catch the record before it goes in, than to insert the record and then have to remove it later.

Adamus

|||

Hi

I don't believe you understand

When i made a post in table one i also make a post in table 2

All works fine

But tabel 2 i also connected with table 3 and here i have in my diagram a cascade delete

So when i delete a record in table 3 it's delete a record or more in table 2
then i have my record in table 1, this i can't delete when i delete the record in table 3
so what i want is to delete all record in table 1 if the record in table 2 are deleted

hop you understand ?

Alvin

|||

Ok table 3 is new...but the answer is still the same...triggers not sp's

When you delete from any table, also delete from other tables...correct?

So you want INSTEAD OF DELETE

Adamus

|||

I try to explain and maybe you can help

In table 1 i have a ID theis ID can bee many times in table 2
Table 2 have also a int there connect to table3

all works fine

When a date field in table3 is over current date then i delete the record in table
And when it does this it also delete the record in table2.

after sometime all the records in table2 there have the ID from table 1 is been deleted
and when there are no more record in table2 there in my feild have the same numbers
as the ID in table 1 then i wnt to delete the record in table 1

Like:

Delete all records in table 1 if table1.ID <> from table2.field

In vbscript i can make a loop to check if the Id from table1 are in table2
if the catch this number = do nothing
if the find the number = delte record

i Can't explain it better. Sorry

Alvin

|||

It looks to me like you're wanting something like this:

create procedure delete_records_from_table1

as

delete from table1

from table1

left join table2

on table1.ID = table2.ID

where table2.ID is null

The left join will include everything in table1, regardless of whether there's a matching record in table2, and then the where clause excludes all of the records where there is a match in table2. This just leaves those records where there's no match in table2, and these can be deleted.

Is this the sort of thing you were looking for?

Iain

|||

Yes yes yes

Thanks

Alvin

sql