Re: Trigger/Foreign Key limitation for real?
From: Steve Kass (skass_at_drew.edu)
Date: 01/11/05
- Next message: Sa: "OPTIMIZER LOCK HINTS"
- Previous message: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- In reply to: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Next in thread: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Reply: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 11 Jan 2005 00:47:40 -0500
Gary,
You can create an INSTEAD OF trigger on the referenced table, not the
referencing table, and the constraint and the trigger can be created in
either
order, I think. The foreign key constraint is declared on the Child
table, not the Parent table, and so the Child table is the one that
can't have
both the trigger and cascade definition. This might not be clear in
Enterprise Manager
or other graphical designers, if the referential integrity is viewed as
just "between the tables".
The restriction in T-SQL is that there cannot be both an INSTEAD OF
UPDATE/DELETE
trigger and a FOREIGN KEY constraint with ON UPDATE/DELETE CASCADE.
In order to do the cascading, a delete is issued on the child table, and
if that is preempted by an INSTEAD OF trigger, the cascading may fail. It's
probably to hard or even impossible to verify the integrity in such a
situation.
SK
Gary K wrote:
>Hiya Steve,
> I haven't fully tested it yet, I only had the thought to 'reverse the
>procedure steps' while I was doing something else so it's not fully tested
>yet. But :-
> I used the SQL Server 2000 Enterprise Manager.
> I created two tables, parent & child. Note that the child table has a
>field in it to match the parent primary key.
> I created a relationship between parent & child. (Cascading U/D)
> I tried to create an instead of update trigger on the parent table. ERR!
> I tried to create an instead of update trigger on the child table. ERR!
> I deleted the relationship.
> I created an instead of update trigger on the parent table. (Containing
>just an update statement to do the real update)
> I created a relationship between parent & child. (Cascading U/D)
> I filled a couple of records in the parent table.
> I updated a couple of records in the parent table.
>
>As you can see it's not yet a full test, and it may not work if there is
>data in the child table, but at least I'm not getting any errors when I try
>to put the trigger and cascade relationship together & update data.
>
>Sorry got to go! late again. I'll be testing this tonight & post a reply
>when I know what's going on.
>
>"Steve Kass" wrote:
>
>
>
>>Gary,
>>
>>I'm not able to do this. Can you post a repro that succeeds?
>>
>>Steve Kass
>>Drew University
>>
>>-- Repro 1: Attempt to create INSTEAD OF DELETE trigger on
>>-- table with ON CASCADE DELETE defined. Error:
>>-- Server: Msg 2113, Level 16, State 1, Procedure Child_p, Line 4
>>-- Cannot CREATE INSTEAD OF DELETE or UPDATE TRIGGER
>>-- 'Child_p' on table 'Child' because the table has a FOREIGN KEY
>>-- with cascaded DELETE or UPDATE.
>>
>>create table Parent (
>> pk int not null primary key
>>)
>>go
>>create table Child (
>> pk int not null primary key,
>> fk int not null
>>)
>>go
>>alter table Child add constraint
>>Child_fk foreign key (fk) references Parent(pk)
>>on delete cascade
>>go
>>create trigger Child_p on Child
>>instead of delete as
>>print 'trigger'
>>go
>>drop table Child
>>Drop table Parent
>>go
>>
>>-- Repro 2: Attempt to add FOREIGN KEY with ON CASCADE DELETE
>>-- on table with an INSTEAD OF DELETE trigger defined. Errors:
>>-- Server: Msg 1787, Level 16, State 1, Line 2
>>-- Cannot define foreign key constraint 'Child_fk' with cascaded
>>-- DELETE or UPDATE on table 'Child' because the table has an
>>-- INSTEAD OF DELETE or UPDATE TRIGGER defined on it.
>>-- Server: Msg 1750, Level 16, State 1, Line 2
>>-- Could not create constraint. See previous errors.
>>create table Parent (
>> pk int not null primary key
>>)
>>go
>>create table Child (
>> pk int not null primary key,
>> fk int not null
>>)
>>go
>>create trigger Child_p on Child
>>instead of delete as
>>print 'trigger'
>>go
>>alter table Child add constraint
>>Child_fk foreign key (fk) references Parent(pk)
>>on delete cascade
>>go
>>drop table Child
>>drop table Parent
>>
>>
>>
>>Gary K wrote:
>>
>>
>>
>>>I'm aware that the documentation says that you cannot create an INSTEAD OF
>>>DELETE/UPDATE trigger on a table with a cascade update/delete relationship
>>>defined. I have even tested this out using the enterprise manager & yes, you
>>>get big error messages when you try.
>>>However, it was during this testing that I discovered that you can create a
>>>cascading update/delete relationship on a table with an INSTEAD OF
>>>DELETE/UPDATE trigger.
>>>I always took the documentation (aided by some newgroup reading) as meaning
>>>that you can either have a cascading relationship OR an INSTEAD OF trigger
>>>but not both. Is this the way I should have been doing it from the start? or
>>>is there a hidden problem that I am not finding?
>>>So far this seems to be performing as expected, (trigger & cascade working
>>>together) but I have only done very minimal testing. I will be checking this
>>>out properly tonight.
>>>
>>>
>>>
>>>
- Next message: Sa: "OPTIMIZER LOCK HINTS"
- Previous message: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- In reply to: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Next in thread: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Reply: Gary K: "Re: Trigger/Foreign Key limitation for real?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
