Re: Trigger/Foreign Key limitation for real?

From: Gary K (GaryK_at_discussions.microsoft.com)
Date: 01/11/05 

Date: Mon, 10 Jan 2005 21:27:01 -0800

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.
> >
> >
>

Relevant Pages

  • Re: Connecting to the Same Table Twice
    ... RelationshipID (primary key) ... RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... you might put spouse names into a separate table linked to this table). ... for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Connecting to the Same Table Twice
    ... > RelationshipID (primary key) ... > RelationshipName (parent, guardian, authorized pick-up, emergency contact, ... > you might put spouse names into a separate table linked to this table). ... > for each link between a child record and a caregiver record, ...
    (microsoft.public.access.gettingstarted)
  • Re: Determine if any foreign keys exist?
    ... >> Best, Hugo ... If I have one parent table and ten child tables, ... >the parent record deleted when no child table references it anymore. ... >option would be to use a trigger on the child tables, ...
    (microsoft.public.sqlserver.programming)
  • Re: MS Access
    ... primary key has some blank, ... We can call this our parent or so called "main" table. ... you must declare a new column in this child table. ... In the above you can tell the customer with an id of 2 has a favorite color ...
    (microsoft.public.access.gettingstarted)
  • Re: Trigger/Foreign Key limitation for real?
    ... You can create an INSTEAD OF trigger on the referenced table, ... The foreign key constraint is declared on the Child ... parent & child. ...
    (microsoft.public.sqlserver.programming)