|
|
You're in the
sql server new users
group:Problem with Script
sql server new users:
Hi
I ran this script to make all my ClientIDs uppercase. UPDATE Clients SET Clients.ClientID = Upper([ClientID]) After it ran, all linked tables had the one ClientID - ZHIR01, which is the last ClientID. The relationships were set to Cascade Update and Delete. Why would this happen? Thanks Vayse
[quoted text, click to view]
On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >Hi >I ran this script to make all my ClientIDs uppercase. >UPDATE Clients SET Clients.ClientID = Upper([ClientID]) > >After it ran, all linked tables had the one ClientID - ZHIR01, which is the >last ClientID. >The relationships were set to Cascade Update and Delete. >Why would this happen? >Thanks >Vayse > Hi Vayse, My first guess is that yoou are bitten by a badly coded trigger. Please check if there's an UPDATE trigger defined for the table. If so, post the trigger's code here. It would also help if you post the table's structure as a CREATE TABLE statement. Make sure to include all properties, constraints, and indexes! --
And indeed it looks like your guess is correct. I won't post the create
table statement here, mostly because it is just too long, as there around 150 fields in it. (which I intend to change) On the clients table, there are 3 triggers - one each for Insert, Delete and Update. Here is part of the Clients_UTrig, the rest of the trigger is just the same code but for different tables. /* * CASCADE UPDATES TO 'Submissions' */ IF UPDATE(ClientID) BEGIN UPDATE Submissions SET Submissions.ClientID = inserted.ClientID FROM Submissions, deleted, inserted WHERE deleted.ClientID = Submissions.ClientID END I've inherited this database, and didn't realise the triggers were there. Though I should have checked! Anyway, this raises an interesting question for me. As I mentioned in the original post, there are relationships set up. Wouldn't the relationship take care of the update and delete? I think I can delete these triggers. Thanks Vayse [quoted text, click to view] "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... > On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: > >>Hi >>I ran this script to make all my ClientIDs uppercase. >>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >> >>After it ran, all linked tables had the one ClientID - ZHIR01, which is >>the >>last ClientID. >>The relationships were set to Cascade Update and Delete. >>Why would this happen? >>Thanks >>Vayse >> > > Hi Vayse, > > My first guess is that yoou are bitten by a badly coded trigger. > > Please check if there's an UPDATE trigger defined for the table. If so, > post the trigger's code here. > > It would also help if you post the table's structure as a CREATE TABLE > statement. Make sure to include all properties, constraints, and > indexes! > > -- > Hugo Kornelis, SQL Server MVP
By itself, the relationship will not do what you desire. You will need to
add CASCADE UPDATE and/or CASCADE DELETE to the foreign key constraint. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous You can't help someone get up a hill without getting a little closer to the top yourself. - H. Norman Schwarzkopf [quoted text, click to view] "Vayse" <vayse@deadspam.com> wrote in message news:uNuc9HN$GHA.4388@TK2MSFTNGP02.phx.gbl... > And indeed it looks like your guess is correct. I won't post the create > table statement here, mostly because it is just too long, as there around > 150 fields in it. (which I intend to change) > On the clients table, there are 3 triggers - one each for Insert, Delete > and Update. > Here is part of the Clients_UTrig, the rest of the trigger is just the > same code but for different tables. > > /* * CASCADE UPDATES TO 'Submissions' */ > IF UPDATE(ClientID) > BEGIN > UPDATE Submissions > SET Submissions.ClientID = inserted.ClientID > FROM Submissions, deleted, inserted > WHERE deleted.ClientID = Submissions.ClientID > END > > I've inherited this database, and didn't realise the triggers were there. > Though I should have checked! > Anyway, this raises an interesting question for me. As I mentioned in the > original post, there are relationships set up. > Wouldn't the relationship take care of the update and delete? I think I > can delete these triggers. > > Thanks > Vayse > > > "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message > news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... >> On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >> >>>Hi >>>I ran this script to make all my ClientIDs uppercase. >>>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >>> >>>After it ran, all linked tables had the one ClientID - ZHIR01, which is >>>the >>>last ClientID. >>>The relationships were set to Cascade Update and Delete. >>>Why would this happen? >>>Thanks >>>Vayse >>> >> >> Hi Vayse, >> >> My first guess is that yoou are bitten by a badly coded trigger. >> >> Please check if there's an UPDATE trigger defined for the table. If so, >> post the trigger's code here. >> >> It would also help if you post the table's structure as a CREATE TABLE >> statement. Make sure to include all properties, constraints, and >> indexes! >> >> -- >> Hugo Kornelis, SQL Server MVP > >
We are together on this, and it sounds like you have set the constraints up
the way you had hoped. By checking the checkboxes in the GUI, you are adding the CASCADE UPDATE and/or CASCADE DELETE to the constraint. -- Arnie Rowland, Ph.D. Westwood Consulting, Inc Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous You can't help someone get up a hill without getting a little closer to the top yourself. - H. Norman Schwarzkopf [quoted text, click to view] "Vayse" <nospam@microsoft.com> wrote in message news:OFzHjlg$GHA.4212@TK2MSFTNGP02.phx.gbl... > I'm a bit confused. Doesn't ticking the Cascade Update and Cascade Delete > in the Database Diagram set those values in the constraint? > > "Arnie Rowland" <arnie@1568.com> wrote in message > news:%23ElKtER$GHA.4712@TK2MSFTNGP03.phx.gbl... >> By itself, the relationship will not do what you desire. You will need to >> add CASCADE UPDATE and/or CASCADE DELETE to the foreign key constraint. >> >> -- >> Arnie Rowland, Ph.D. >> Westwood Consulting, Inc >> >> Most good judgment comes from experience. >> Most experience comes from bad judgment. >> - Anonymous >> >> You can't help someone get up a hill without getting a little closer to >> the top yourself. >> - H. Norman Schwarzkopf >> >> >> "Vayse" <vayse@deadspam.com> wrote in message >> news:uNuc9HN$GHA.4388@TK2MSFTNGP02.phx.gbl... >>> And indeed it looks like your guess is correct. I won't post the create >>> table statement here, mostly because it is just too long, as there >>> around 150 fields in it. (which I intend to change) >>> On the clients table, there are 3 triggers - one each for Insert, Delete >>> and Update. >>> Here is part of the Clients_UTrig, the rest of the trigger is just the >>> same code but for different tables. >>> >>> /* * CASCADE UPDATES TO 'Submissions' */ >>> IF UPDATE(ClientID) >>> BEGIN >>> UPDATE Submissions >>> SET Submissions.ClientID = inserted.ClientID >>> FROM Submissions, deleted, inserted >>> WHERE deleted.ClientID = Submissions.ClientID >>> END >>> >>> I've inherited this database, and didn't realise the triggers were >>> there. Though I should have checked! >>> Anyway, this raises an interesting question for me. As I mentioned in >>> the original post, there are relationships set up. >>> Wouldn't the relationship take care of the update and delete? I think I >>> can delete these triggers. >>> >>> Thanks >>> Vayse >>> >>> >>> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message >>> news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... >>>> On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >>>> >>>>>Hi >>>>>I ran this script to make all my ClientIDs uppercase. >>>>>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >>>>> >>>>>After it ran, all linked tables had the one ClientID - ZHIR01, which is >>>>>the >>>>>last ClientID. >>>>>The relationships were set to Cascade Update and Delete. >>>>>Why would this happen? >>>>>Thanks >>>>>Vayse >>>>> >>>> >>>> Hi Vayse, >>>> >>>> My first guess is that yoou are bitten by a badly coded trigger. >>>> >>>> Please check if there's an UPDATE trigger defined for the table. If so, >>>> post the trigger's code here. >>>> >>>> It would also help if you post the table's structure as a CREATE TABLE >>>> statement. Make sure to include all properties, constraints, and >>>> indexes! >>>> >>>> -- >>>> Hugo Kornelis, SQL Server MVP >>> >>> >> >> > >
I'm a bit confused. Doesn't ticking the Cascade Update and Cascade Delete in
the Database Diagram set those values in the constraint? [quoted text, click to view] "Arnie Rowland" <arnie@1568.com> wrote in message news:%23ElKtER$GHA.4712@TK2MSFTNGP03.phx.gbl... > By itself, the relationship will not do what you desire. You will need to > add CASCADE UPDATE and/or CASCADE DELETE to the foreign key constraint. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > You can't help someone get up a hill without getting a little closer to > the top yourself. > - H. Norman Schwarzkopf > > > "Vayse" <vayse@deadspam.com> wrote in message > news:uNuc9HN$GHA.4388@TK2MSFTNGP02.phx.gbl... >> And indeed it looks like your guess is correct. I won't post the create >> table statement here, mostly because it is just too long, as there around >> 150 fields in it. (which I intend to change) >> On the clients table, there are 3 triggers - one each for Insert, Delete >> and Update. >> Here is part of the Clients_UTrig, the rest of the trigger is just the >> same code but for different tables. >> >> /* * CASCADE UPDATES TO 'Submissions' */ >> IF UPDATE(ClientID) >> BEGIN >> UPDATE Submissions >> SET Submissions.ClientID = inserted.ClientID >> FROM Submissions, deleted, inserted >> WHERE deleted.ClientID = Submissions.ClientID >> END >> >> I've inherited this database, and didn't realise the triggers were there. >> Though I should have checked! >> Anyway, this raises an interesting question for me. As I mentioned in the >> original post, there are relationships set up. >> Wouldn't the relationship take care of the update and delete? I think I >> can delete these triggers. >> >> Thanks >> Vayse >> >> >> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message >> news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... >>> On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >>> >>>>Hi >>>>I ran this script to make all my ClientIDs uppercase. >>>>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >>>> >>>>After it ran, all linked tables had the one ClientID - ZHIR01, which is >>>>the >>>>last ClientID. >>>>The relationships were set to Cascade Update and Delete. >>>>Why would this happen? >>>>Thanks >>>>Vayse >>>> >>> >>> Hi Vayse, >>> >>> My first guess is that yoou are bitten by a badly coded trigger. >>> >>> Please check if there's an UPDATE trigger defined for the table. If so, >>> post the trigger's code here. >>> >>> It would also help if you post the table's structure as a CREATE TABLE >>> statement. Make sure to include all properties, constraints, and >>> indexes! >>> >>> -- >>> Hugo Kornelis, SQL Server MVP >> >> > >
Thanks!
[quoted text, click to view] "Arnie Rowland" <arnie@1568.com> wrote in message news:uDmtzug$GHA.4328@TK2MSFTNGP03.phx.gbl... > We are together on this, and it sounds like you have set the constraints > up the way you had hoped. > > By checking the checkboxes in the GUI, you are adding the CASCADE UPDATE > and/or CASCADE DELETE to the constraint. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > You can't help someone get up a hill without getting a little closer to > the top yourself. > - H. Norman Schwarzkopf > > > "Vayse" <nospam@microsoft.com> wrote in message > news:OFzHjlg$GHA.4212@TK2MSFTNGP02.phx.gbl... >> I'm a bit confused. Doesn't ticking the Cascade Update and Cascade Delete >> in the Database Diagram set those values in the constraint? >> >> "Arnie Rowland" <arnie@1568.com> wrote in message >> news:%23ElKtER$GHA.4712@TK2MSFTNGP03.phx.gbl... >>> By itself, the relationship will not do what you desire. You will need >>> to add CASCADE UPDATE and/or CASCADE DELETE to the foreign key >>> constraint. >>> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> You can't help someone get up a hill without getting a little closer to >>> the top yourself. >>> - H. Norman Schwarzkopf >>> >>> >>> "Vayse" <vayse@deadspam.com> wrote in message >>> news:uNuc9HN$GHA.4388@TK2MSFTNGP02.phx.gbl... >>>> And indeed it looks like your guess is correct. I won't post the create >>>> table statement here, mostly because it is just too long, as there >>>> around 150 fields in it. (which I intend to change) >>>> On the clients table, there are 3 triggers - one each for Insert, >>>> Delete and Update. >>>> Here is part of the Clients_UTrig, the rest of the trigger is just the >>>> same code but for different tables. >>>> >>>> /* * CASCADE UPDATES TO 'Submissions' */ >>>> IF UPDATE(ClientID) >>>> BEGIN >>>> UPDATE Submissions >>>> SET Submissions.ClientID = inserted.ClientID >>>> FROM Submissions, deleted, inserted >>>> WHERE deleted.ClientID = Submissions.ClientID >>>> END >>>> >>>> I've inherited this database, and didn't realise the triggers were >>>> there. Though I should have checked! >>>> Anyway, this raises an interesting question for me. As I mentioned in >>>> the original post, there are relationships set up. >>>> Wouldn't the relationship take care of the update and delete? I think I >>>> can delete these triggers. >>>> >>>> Thanks >>>> Vayse >>>> >>>> >>>> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message >>>> news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... >>>>> On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >>>>> >>>>>>Hi >>>>>>I ran this script to make all my ClientIDs uppercase. >>>>>>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >>>>>> >>>>>>After it ran, all linked tables had the one ClientID - ZHIR01, which >>>>>>is the >>>>>>last ClientID. >>>>>>The relationships were set to Cascade Update and Delete. >>>>>>Why would this happen? >>>>>>Thanks >>>>>>Vayse >>>>>> >>>>> >>>>> Hi Vayse, >>>>> >>>>> My first guess is that yoou are bitten by a badly coded trigger. >>>>> >>>>> Please check if there's an UPDATE trigger defined for the table. If >>>>> so, >>>>> post the trigger's code here. >>>>> >>>>> It would also help if you post the table's structure as a CREATE TABLE >>>>> statement. Make sure to include all properties, constraints, and >>>>> indexes! >>>>> >>>>> -- >>>>> Hugo Kornelis, SQL Server MVP >>>> >>>> >>> >>> >> >> > >
Thanks!
[quoted text, click to view] "Arnie Rowland" <arnie@1568.com> wrote in message news:uDmtzug$GHA.4328@TK2MSFTNGP03.phx.gbl... > We are together on this, and it sounds like you have set the constraints > up the way you had hoped. > > By checking the checkboxes in the GUI, you are adding the CASCADE UPDATE > and/or CASCADE DELETE to the constraint. > > -- > Arnie Rowland, Ph.D. > Westwood Consulting, Inc > > Most good judgment comes from experience. > Most experience comes from bad judgment. > - Anonymous > > You can't help someone get up a hill without getting a little closer to > the top yourself. > - H. Norman Schwarzkopf > > > "Vayse" <nospam@microsoft.com> wrote in message > news:OFzHjlg$GHA.4212@TK2MSFTNGP02.phx.gbl... >> I'm a bit confused. Doesn't ticking the Cascade Update and Cascade Delete >> in the Database Diagram set those values in the constraint? >> >> "Arnie Rowland" <arnie@1568.com> wrote in message >> news:%23ElKtER$GHA.4712@TK2MSFTNGP03.phx.gbl... >>> By itself, the relationship will not do what you desire. You will need >>> to add CASCADE UPDATE and/or CASCADE DELETE to the foreign key >>> constraint. >>> >>> -- >>> Arnie Rowland, Ph.D. >>> Westwood Consulting, Inc >>> >>> Most good judgment comes from experience. >>> Most experience comes from bad judgment. >>> - Anonymous >>> >>> You can't help someone get up a hill without getting a little closer to >>> the top yourself. >>> - H. Norman Schwarzkopf >>> >>> >>> "Vayse" <vayse@deadspam.com> wrote in message >>> news:uNuc9HN$GHA.4388@TK2MSFTNGP02.phx.gbl... >>>> And indeed it looks like your guess is correct. I won't post the create >>>> table statement here, mostly because it is just too long, as there >>>> around 150 fields in it. (which I intend to change) >>>> On the clients table, there are 3 triggers - one each for Insert, >>>> Delete and Update. >>>> Here is part of the Clients_UTrig, the rest of the trigger is just the >>>> same code but for different tables. >>>> >>>> /* * CASCADE UPDATES TO 'Submissions' */ >>>> IF UPDATE(ClientID) >>>> BEGIN >>>> UPDATE Submissions >>>> SET Submissions.ClientID = inserted.ClientID >>>> FROM Submissions, deleted, inserted >>>> WHERE deleted.ClientID = Submissions.ClientID >>>> END >>>> >>>> I've inherited this database, and didn't realise the triggers were >>>> there. Though I should have checked! >>>> Anyway, this raises an interesting question for me. As I mentioned in >>>> the original post, there are relationships set up. >>>> Wouldn't the relationship take care of the update and delete? I think I >>>> can delete these triggers. >>>> >>>> Thanks >>>> Vayse >>>> >>>> >>>> "Hugo Kornelis" <hugo@perFact.REMOVETHIS.info.INVALID> wrote in message >>>> news:hsl7k2ldpebmf9sptqr378qoccr3ct2drv@4ax.com... >>>>> On Fri, 27 Oct 2006 15:27:09 +0100, Vayse wrote: >>>>> >>>>>>Hi >>>>>>I ran this script to make all my ClientIDs uppercase. >>>>>>UPDATE Clients SET Clients.ClientID = Upper([ClientID]) >>>>>> >>>>>>After it ran, all linked tables had the one ClientID - ZHIR01, which >>>>>>is the >>>>>>last ClientID. >>>>>>The relationships were set to Cascade Update and Delete. >>>>>>Why would this happen? >>>>>>Thanks >>>>>>Vayse >>>>>> >>>>> >>>>> Hi Vayse, >>>>> >>>>> My first guess is that yoou are bitten by a badly coded trigger. >>>>> >>>>> Please check if there's an UPDATE trigger defined for the table. If >>>>> so, >>>>> post the trigger's code here. >>>>> >>>>> It would also help if you post the table's structure as a CREATE TABLE >>>>> statement. Make sure to include all properties, constraints, and >>>>> indexes! >>>>> >>>>> -- >>>>> Hugo Kornelis, SQL Server MVP >>>> >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
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
| home · blog · groups | Questions? Comments? Contact the dn |