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