Short answer: you can't. Its an inherant flaw in MS/Sybase SQL Server.
And most others, too - No before + after triggers, only after triggers
Long answer:
begin transaction
--get the PK
delete from childtable where K = thePK
delete from parent where PK = thePK
commit transaction
yes, it IS that painfull.
N
Tony Blomfield wrote:
>
> HI.
>
> With Interbase it is a breeze to do cascade deletes using triggers because
> of IB's concept of New and Old.
>
> With SQL7 it seems nobody thought of this, because the delete is attempted
> BEFORE the trigger is called.
>
> Consider: T1 is parent, T2 is Child with FK constraint to T1
> If I write a simple trigger. The PK for T1 is called T1ID
>
> create trigger DelT1 on T1
> for delete as
> declare @T1ID int
> begin
> /* now casacade */
>
> select @T1ID = T1IDSecurityGroupID from Deleted;
> delete from T2 where T2ID = @T1ID;
> end
>
> When I execute Delete from T1 where T1ID = 3
>
> I allways get the FK Constraint Violation, and my delete trigger is not
> called.
>
> QUESTION
>
> How do I get a delete trigger to run before SQL7 actually does the delete,
> or How do others handle cascade deletes in SQL7?
>
> TIA,
>
> Tony.
>
> ---------------------------------------------------------------------------
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
--
Nic Wise - Inprise New Zealand / Brocker Technologies Web Monkey.
mob:+64.21.676.418 - wk:+64.9.481.9999 x9753 - wk-em:[EMAIL PROTECTED]
hm: +64.9.277.5309 - hm-em:[EMAIL PROTECTED]
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
