|
|
You're in the
sql server programming
group:Trigger/Foreign Key limitation for real?
sql server programming:
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
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. [quoted text, click to view] "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. > > > >
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 [quoted text, click to view] 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. >
Yup, thought so. I knew i was jumping the gun. I'll have to double check the
database i created at work to see what i was doing, but i tried to replicate it at home every which way with all the tools i could find with no success. (Knew it was futile, but you got to find out for yourself) Sorry about this, next time i'll double check things at home instead of doing it all half asleep at work :) [quoted text, click to view] "Steve Kass" wrote:
> 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. > >>> > >>> > >>> > >>>
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 [quoted text, click to view] 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. >>> >>> >>>
Don't see what you're looking for? Try a search.
|
January 2003
March 2003
April 2003
May 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
April 2008
August 2008
| home · blog · groups | Questions? Comments? Contact the dn |