What is the best way to implement cascading deletes/ By implementing delete
triggers????
How?
Can anyone suggest where to find a code example (MS SQL Server 6.5) or
suggest another listserve where a question could be poised.
We've tried the following code, it executes without error, however does not
seem to be firing when a DELETE is executed against the Speciment_test
table.
[EMAIL PROTECTED]
Lester LaForce
Automated Management Systems
Armed Forces Institute of Pathology
Walter Reed Army Hospital
Washington DC
drop trigger tD_specimen_test
go
create trigger tD_specimen_test on specimen_test for DELETE as
/* DELETE trigger on specimen_test */
begin
declare @numrows int,
@errno int,
@errmsg varchar(255),
@deletecnt int,
@deleted_specimen_id int
SELECT @numrows = @@rowcount
/* If no row(s) to be deleted, return */
IF @@rowcount > 0
begin
SELECT @deleted_specimen_id specimen_id
FROM deleted
SELECT @deletecnt = count(*)
FROM specimenlog_test
WHERE specimenlog_test .specimen_id = @deleted_specimen_id
IF @deletecnt > 0
BEGIN
DELETE FROM specimenlog_test
WHERE specimenlog_test .specimen_id =
@deleted_specimen_id
IF @@error <> 0
BEGIN
/* ROLLBACK TRIGGER WITH RAISERROR 25055 'Error
deleting Specimen Log row(s)' */
select @errno = 30001,
@errmsg = 'Error deleting Specimen Log row(s).'
goto error
END
END
end
error:
raiserror @errno @errmsg
rollback transaction
end
go
> [EMAIL PROTECTED] HOSTED BY IIGG, INC. FOR HELP WITH LIST SERVE COMMANDS, ADDRESS
> A MESSAGE TO [EMAIL PROTECTED] WITH THE FOLLOWING MESSAGE: help pfcsig
> SEND ALL OTHER INQUIRES TO [EMAIL PROTECTED]
