Delete Triggers in 7.0 (only after?)
-
I am right in assuming that under SQL Server 7.0, my triggers execute as 'after' triggers? I've written a bunch of triggers that were designed to run under MSS 2000 to delete child records from referencing tables, but I ended up deploying them to a 7.0 server, and they don't appear to be working.
-
instead of triggers were introduced in sql 2k. What's the problem with the code you created?
-
(to clarify, this is for a SQL Server 7.0 system, which now looking at this forum, is probably mis-posted)
well, basically, here's the story,
there's a table called CA_Categories what has a field called CAT_CODE.
I created this table:
create table dbo.SeeAlsoCategory
(
sacKey int identity not null,
sacCategoryKey nvarchar(2) not null,
sacSacCategoryKey nvarchar(2) not null
)
go
alter table SeeAlsoCategory add constraint pkSacKey primary key nonclustered (sacKey)
go
alter table SeeAlsoCategory add constraint uqSacCatAndSacCat unique(sacCategoryKey, sacSacCategoryKey)
go
alter table SeeAlsoCategory add
constraint fkSacCategoryKey foreign key (sacCategoryKey) references CA_Categories(CAT_CODE)
go
alter table SeeAlsoCategory add
constraint fkSacSacCategoryKey foreign key (sacSacCategoryKey) references CA_Categories(CAT_CODE)
go
then I created a trigger on CA_Categories that looks like this:
create trigger trgCA_Categories_SeeAlsoCats_OnDelete
on CA_Categories for delete
as
delete from SeeAlsoCategory from SeeAlsoCategory
join Deleted on SeeAlsoCategory.sacCategoryKey = Deleted.CAT_CODE
delete from SeeAlsoCategory from SeeAlsoCategory
join Deleted on SeeAlsoCategory.sacSacCategoryKey = Deleted.CAT_CODE
go
... with the expectation that it would do the work of deleting my child records. But it appears that I was mistaken.
-
Hmm, you'd have to write a recursive sp to delete the absolete items (assuming you have no pre-defined limit to the recursive level). I think it would be simpler to change the foreign key to on delete cascade. That way sql server would do the work for you.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply