Very Computer

I'm using SQL Server 7.0 SP2.

My problem is with a delete trigger and Foreign Keys.

I have 2 tabes (T1, T2)

T1 is the parent table and T2 is the child table in my FK named FKT2_TO_T1
on table T2.  I have a delete trigger on T1 that says:

CREATE TRIGGER T1_DELETE ON [T1]
FOR DELETE AS

DELETE  T2
FROM  T2 AS T2 JOIN deleted  AS del
ON  T2.date_dw_valuation_dte = del.calendar_date

When I delete a record from T1, I get the error

Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint 'FKT2_TO_T1'.
The conflict occurred in database 'Sid, table 'T2', column 'Calendar_Date'.
The statement has been terminated.

Should I not be able to delete a record from a Parent table as long as there
is a trigger to delete the corresponding Child table records?  Am I missing
something?

Any help is greatly appreciated.

Thanks in advance,
Darrin

Constraints (FK in this case) are checked (enforced) before triggers.

--

Kresimir Radosevic, SQL MVP

I support the Professional Association for SQL Server and its user community
of dedicated professionals.
www.sqlpass.org

Quote:> Hi,

> I'm using SQL Server 7.0 SP2.

> My problem is with a delete trigger and Foreign Keys.

> I have 2 tabes (T1, T2)

> T1 is the parent table and T2 is the child table in my FK named FKT2_TO_T1
> on table T2.  I have a delete trigger on T1 that says:

> CREATE TRIGGER T1_DELETE ON [T1]
> FOR DELETE AS

> DELETE  T2
> FROM  T2 AS T2 JOIN deleted  AS del
> ON  T2.date_dw_valuation_dte = del.calendar_date

> When I delete a record from T1, I get the error

> Server: Msg 547, Level 16, State 1, Line 1
> DELETE statement conflicted with COLUMN REFERENCE constraint 'FKT2_TO_T1'.
> The conflict occurred in database 'Sid, table 'T2', column
> 'Calendar_Date'.
> The statement has been terminated.

> Should I not be able to delete a record from a Parent table as long as
> there
> is a trigger to delete the corresponding Child table records?  Am I
> missing
> something?

> Any help is greatly appreciated.

> Thanks in advance,
> Darrin

In v7 only after triggers are available.
In v2k you could do this with an instead of trigger.

Quote:>-----Original Message-----
>Hi,

>I'm using SQL Server 7.0 SP2.

>My problem is with a delete trigger and Foreign Keys.

>I have 2 tabes (T1, T2)

>T1 is the parent table and T2 is the child table in my FK
named FKT2_TO_T1
>on table T2.  I have a delete trigger on T1 that says:

>CREATE TRIGGER T1_DELETE ON [T1]
>FOR DELETE AS

>DELETE  T2
>FROM  T2 AS T2 JOIN deleted  AS del
>ON  T2.date_dw_valuation_dte = del.calendar_date

>When I delete a record from T1, I get the error

>Server: Msg 547, Level 16, State 1, Line 1
>DELETE statement conflicted with COLUMN REFERENCE

Quote:>The conflict occurred in database 'Sid, table 'T2',

Quote:>The statement has been terminated.

>Should I not be able to delete a record from a Parent

Quote:>is a trigger to delete the corresponding Child table

Quote:>something?

>Any help is greatly appreciated.

>Thanks in advance,
>Darrin

>.