|
|
You're in the
sql server programming
group:INSTEAD OF trigger not quite working ...
sql server programming:
Hi Chris Please show us the CREATE TABLE statement you are using, with the default value for TypeID. -- HTH Kalen Delaney, SQL Server MVP www.solidqualitylearning.com [quoted text, click to view] "Chris Smith" <chris.smith@shaw.ca> wrote in message news:x01sf.193830$ki.77280@pd7tw2no... > > I HAVE put the newid() default value in for TypeID, but it does not seem > to function. > > Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for Desc. > > > "Erland Sommarskog" <esquel@sommarskog.se> wrote in message > news:Xns973924CECC6Yazorman@127.0.0.1... >> Chris Smith (chris.smith@shaw.ca) writes: >>> I am trying to create an INSTEAD OF trigger on a table that only has two >>> fields: an ID and a description. The following code is not quite doing, >>> well, anything that I have been able to figure out ... >> >> It would certainly help if you included the definition of BookTypes >> and the INSERT statement you are using. >> >>> INSERT INTO BookTypes >>> SELECT NewID() as TypeID, TypeDesc >>> FROM inserted >> >> A general comment on style here: always provide a column list for your >> INSERT statements. If another column is added to the table, this INSERT >> statement will fail. >> >> By the way, why not simply use NEWID() as the default value for TypeID? >> >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >> >> Books Online for SQL Server 2005 at >> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >> Books Online for SQL Server 2000 at >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > >
I am trying to create an INSTEAD OF trigger on a table that only has two
fields: an ID and a description. The following code is not quite doing, well, anything that I have been able to figure out ... CREATE TRIGGER InsteadInsertTrigger_BookTypes on BookTypes INSTEAD OF INSERT AS BEGIN -- The only column in the inserted table is the TypeDesc -- Therefore we need to generate the UniqueID on behalf -- of the application ... INSERT INTO BookTypes SELECT NewID() as TypeID, TypeDesc FROM inserted END go Where have I gone wrong here ?
Chris Smith (chris.smith@shaw.ca) writes:
[quoted text, click to view] > I am trying to create an INSTEAD OF trigger on a table that only has two > fields: an ID and a description. The following code is not quite doing, > well, anything that I have been able to figure out ... It would certainly help if you included the definition of BookTypes and the INSERT statement you are using. [quoted text, click to view] > INSERT INTO BookTypes > SELECT NewID() as TypeID, TypeDesc > FROM inserted A general comment on style here: always provide a column list for your INSERT statements. If another column is added to the table, this INSERT statement will fail. By the way, why not simply use NEWID() as the default value for TypeID? -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
I HAVE put the newid() default value in for TypeID, but it does not seem to
function. Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for Desc. [quoted text, click to view] "Erland Sommarskog" <esquel@sommarskog.se> wrote in message news:Xns973924CECC6Yazorman@127.0.0.1... > Chris Smith (chris.smith@shaw.ca) writes: >> I am trying to create an INSTEAD OF trigger on a table that only has two >> fields: an ID and a description. The following code is not quite doing, >> well, anything that I have been able to figure out ... > > It would certainly help if you included the definition of BookTypes > and the INSERT statement you are using. > >> INSERT INTO BookTypes >> SELECT NewID() as TypeID, TypeDesc >> FROM inserted > > A general comment on style here: always provide a column list for your > INSERT statements. If another column is added to the table, this INSERT > statement will fail. > > By the way, why not simply use NEWID() as the default value for TypeID? > > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Chris Smith (chris.smith@shaw.ca) writes:
[quoted text, click to view] > I HAVE put the newid() default value in for TypeID, but it does not seem > to function. This works for me: CREATE TABLE chris (clé uniqueidentifier NOT NULL CONSTRAINT chris_newid DEFAULT newid(), data int NOT NULL) go INSERT chris (data) Values (4711) go SELECT * FROM chris -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
The table was originally created as a simple little table, and with time I
have added a couple of things with alter table - alter column and triggers. The Create Table statement now looks like: ************** USE [MediaCollectionData] GO /****** Object: Table [dbo].[BookTypes] Script Date: 12/27/2005 17:59:42 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[BookTypes]( [TypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DefaultType] DEFAULT (newid()), [TypeDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_UniqueBookTypes] PRIMARY KEY CLUSTERED ( [TypeID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] ************** [quoted text, click to view] "Kalen Delaney" <replies@public_newsgroups.com> wrote in message news:uCBnNmpCGHA.3820@TK2MSFTNGP12.phx.gbl... > > Hi Chris > > Please show us the CREATE TABLE statement you are using, with the default > value for TypeID. > > -- > HTH > Kalen Delaney, SQL Server MVP > www.solidqualitylearning.com > > > "Chris Smith" <chris.smith@shaw.ca> wrote in message > news:x01sf.193830$ki.77280@pd7tw2no... >> >> I HAVE put the newid() default value in for TypeID, but it does not seem >> to function. >> >> Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for >> Desc. >> >> >> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >> news:Xns973924CECC6Yazorman@127.0.0.1... >>> Chris Smith (chris.smith@shaw.ca) writes: >>>> I am trying to create an INSTEAD OF trigger on a table that only has >>>> two >>>> fields: an ID and a description. The following code is not quite doing, >>>> well, anything that I have been able to figure out ... >>> >>> It would certainly help if you included the definition of BookTypes >>> and the INSERT statement you are using. >>> >>>> INSERT INTO BookTypes >>>> SELECT NewID() as TypeID, TypeDesc >>>> FROM inserted >>> >>> A general comment on style here: always provide a column list for your >>> INSERT statements. If another column is added to the table, this INSERT >>> statement will fail. >>> >>> By the way, why not simply use NEWID() as the default value for TypeID? >>> >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>> >>> Books Online for SQL Server 2005 at >>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>> Books Online for SQL Server 2000 at >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> >> >> > > >
Most likely, you have an incorrect INSERT statement. My guess is that your INSERT statement
specifies a value (possibly NULL) for the TypeID column, which is why the default isn't respected. I executed your CREATE TABLE statement, and then the below INSERT statement: INSERT INTO BookTypes (TypeDesc) VALUES('Hello') A values was indeed generated for the TypeID column. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Chris Smith" <chris.smith@shaw.ca> wrote in message news:Otlsf.209874$Gd6.187567@pd7tw3no...
> The table was originally created as a simple little table, and with time I have added a couple of > things with alter table - alter column and triggers. The Create Table statement now looks like: > ************** > > USE [MediaCollectionData] > > GO > > /****** Object: Table [dbo].[BookTypes] Script Date: 12/27/2005 17:59:42 ******/ > > SET ANSI_NULLS ON > > GO > > SET QUOTED_IDENTIFIER ON > > GO > > CREATE TABLE [dbo].[BookTypes]( > > [TypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DefaultType] DEFAULT (newid()), > > [TypeDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, > > CONSTRAINT [PK_UniqueBookTypes] PRIMARY KEY CLUSTERED > > ( > > [TypeID] ASC > > )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] > > ) ON [PRIMARY] > > > > ************** > "Kalen Delaney" <replies@public_newsgroups.com> wrote in message > news:uCBnNmpCGHA.3820@TK2MSFTNGP12.phx.gbl... >> >> Hi Chris >> >> Please show us the CREATE TABLE statement you are using, with the default value for TypeID. >> >> -- >> HTH >> Kalen Delaney, SQL Server MVP >> www.solidqualitylearning.com >> >> >> "Chris Smith" <chris.smith@shaw.ca> wrote in message news:x01sf.193830$ki.77280@pd7tw2no... >>> >>> I HAVE put the newid() default value in for TypeID, but it does not seem to function. >>> >>> Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for Desc. >>> >>> >>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >>> news:Xns973924CECC6Yazorman@127.0.0.1... >>>> Chris Smith (chris.smith@shaw.ca) writes: >>>>> I am trying to create an INSTEAD OF trigger on a table that only has two >>>>> fields: an ID and a description. The following code is not quite doing, >>>>> well, anything that I have been able to figure out ... >>>> >>>> It would certainly help if you included the definition of BookTypes >>>> and the INSERT statement you are using. >>>> >>>>> INSERT INTO BookTypes >>>>> SELECT NewID() as TypeID, TypeDesc >>>>> FROM inserted >>>> >>>> A general comment on style here: always provide a column list for your >>>> INSERT statements. If another column is added to the table, this INSERT >>>> statement will fail. >>>> >>>> By the way, why not simply use NEWID() as the default value for TypeID? >>>> >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>>> >>>> Books Online for SQL Server 2005 at >>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>> Books Online for SQL Server 2000 at >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >>> >>> >> >> >> > >
That's interesting to "hear". The insert statement is being generated behind
the scenes in a VB.Net program. I'll have to do some digging on this. But I have a new direction now. Thank you. "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message news:uGPHxH5CGHA.1124@TK2MSFTNGP10.phx.gbl... [quoted text, click to view] > Most likely, you have an incorrect INSERT statement. My guess is that your > INSERT statement specifies a value (possibly NULL) for the TypeID column, > which is why the default isn't respected. I executed your CREATE TABLE > statement, and then the below INSERT statement: > > INSERT INTO BookTypes (TypeDesc) VALUES('Hello') > > A values was indeed generated for the TypeID column. > > -- > Tibor Karaszi, SQL Server MVP > http://www.karaszi.com/sqlserver/default.asp > http://www.solidqualitylearning.com/ > Blog: http://solidqualitylearning.com/blogs/tibor/ > > > "Chris Smith" <chris.smith@shaw.ca> wrote in message > news:Otlsf.209874$Gd6.187567@pd7tw3no... >> The table was originally created as a simple little table, and with time >> I have added a couple of things with alter table - alter column and >> triggers. The Create Table statement now looks like: >> ************** >> >> USE [MediaCollectionData] >> >> GO >> >> /****** Object: Table [dbo].[BookTypes] Script Date: 12/27/2005 17:59:42 >> ******/ >> >> SET ANSI_NULLS ON >> >> GO >> >> SET QUOTED_IDENTIFIER ON >> >> GO >> >> CREATE TABLE [dbo].[BookTypes]( >> >> [TypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DefaultType] DEFAULT >> (newid()), >> >> [TypeDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, >> >> CONSTRAINT [PK_UniqueBookTypes] PRIMARY KEY CLUSTERED >> >> ( >> >> [TypeID] ASC >> >> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] >> >> ) ON [PRIMARY] >> >> >> >> ************** >> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >> news:uCBnNmpCGHA.3820@TK2MSFTNGP12.phx.gbl... >>> >>> Hi Chris >>> >>> Please show us the CREATE TABLE statement you are using, with the >>> default value for TypeID. >>> >>> -- >>> HTH >>> Kalen Delaney, SQL Server MVP >>> www.solidqualitylearning.com >>> >>> >>> "Chris Smith" <chris.smith@shaw.ca> wrote in message >>> news:x01sf.193830$ki.77280@pd7tw2no... >>>> >>>> I HAVE put the newid() default value in for TypeID, but it does not >>>> seem to function. >>>> >>>> Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for >>>> Desc. >>>> >>>> >>>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >>>> news:Xns973924CECC6Yazorman@127.0.0.1... >>>>> Chris Smith (chris.smith@shaw.ca) writes: >>>>>> I am trying to create an INSTEAD OF trigger on a table that only has >>>>>> two >>>>>> fields: an ID and a description. The following code is not quite >>>>>> doing, >>>>>> well, anything that I have been able to figure out ... >>>>> >>>>> It would certainly help if you included the definition of BookTypes >>>>> and the INSERT statement you are using. >>>>> >>>>>> INSERT INTO BookTypes >>>>>> SELECT NewID() as TypeID, TypeDesc >>>>>> FROM inserted >>>>> >>>>> A general comment on style here: always provide a column list for your >>>>> INSERT statements. If another column is added to the table, this >>>>> INSERT >>>>> statement will fail. >>>>> >>>>> By the way, why not simply use NEWID() as the default value for >>>>> TypeID? >>>>> >>>>> >>>>> >>>>> -- >>>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>>>> >>>>> Books Online for SQL Server 2005 at >>>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>>> Books Online for SQL Server 2000 at >>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>> >>>> >>>> >>> >>> >>> >> >> >
[quoted text, click to view]
> The insert statement is being generated behind the scenes in a VB.Net program. I would have guesses that. :-) You can use Profiler to catch the insert statement. And then dig into ADO.NET and see how you can make the INSERT leave out that column (or possibly post in an ADO.NET group). -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ [quoted text, click to view] "Chris Smith" <chris.smith@shaw.ca> wrote in message news:Fczsf.91873$2k.19886@pd7tw1no...
> That's interesting to "hear". The insert statement is being generated behind the scenes in a > VB.Net program. > > I'll have to do some digging on this. But I have a new direction now. Thank you. > > > "Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in message > news:uGPHxH5CGHA.1124@TK2MSFTNGP10.phx.gbl... >> Most likely, you have an incorrect INSERT statement. My guess is that your INSERT statement >> specifies a value (possibly NULL) for the TypeID column, which is why the default isn't >> respected. I executed your CREATE TABLE statement, and then the below INSERT statement: >> >> INSERT INTO BookTypes (TypeDesc) VALUES('Hello') >> >> A values was indeed generated for the TypeID column. >> >> -- >> Tibor Karaszi, SQL Server MVP >> http://www.karaszi.com/sqlserver/default.asp >> http://www.solidqualitylearning.com/ >> Blog: http://solidqualitylearning.com/blogs/tibor/ >> >> >> "Chris Smith" <chris.smith@shaw.ca> wrote in message news:Otlsf.209874$Gd6.187567@pd7tw3no... >>> The table was originally created as a simple little table, and with time I have added a couple >>> of things with alter table - alter column and triggers. The Create Table statement now looks >>> like: >>> ************** >>> >>> USE [MediaCollectionData] >>> >>> GO >>> >>> /****** Object: Table [dbo].[BookTypes] Script Date: 12/27/2005 17:59:42 ******/ >>> >>> SET ANSI_NULLS ON >>> >>> GO >>> >>> SET QUOTED_IDENTIFIER ON >>> >>> GO >>> >>> CREATE TABLE [dbo].[BookTypes]( >>> >>> [TypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DefaultType] DEFAULT (newid()), >>> >>> [TypeDesc] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, >>> >>> CONSTRAINT [PK_UniqueBookTypes] PRIMARY KEY CLUSTERED >>> >>> ( >>> >>> [TypeID] ASC >>> >>> )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] >>> >>> ) ON [PRIMARY] >>> >>> >>> >>> ************** >>> "Kalen Delaney" <replies@public_newsgroups.com> wrote in message >>> news:uCBnNmpCGHA.3820@TK2MSFTNGP12.phx.gbl... >>>> >>>> Hi Chris >>>> >>>> Please show us the CREATE TABLE statement you are using, with the default value for TypeID. >>>> >>>> -- >>>> HTH >>>> Kalen Delaney, SQL Server MVP >>>> www.solidqualitylearning.com >>>> >>>> >>>> "Chris Smith" <chris.smith@shaw.ca> wrote in message news:x01sf.193830$ki.77280@pd7tw2no... >>>>> >>>>> I HAVE put the newid() default value in for TypeID, but it does not seem to function. >>>>> >>>>> Field definitions are UniqueIdenfier for TypeID, and Varchar(50) for Desc. >>>>> >>>>> >>>>> "Erland Sommarskog" <esquel@sommarskog.se> wrote in message >>>>> news:Xns973924CECC6Yazorman@127.0.0.1... >>>>>> Chris Smith (chris.smith@shaw.ca) writes: >>>>>>> I am trying to create an INSTEAD OF trigger on a table that only has two >>>>>>> fields: an ID and a description. The following code is not quite doing, >>>>>>> well, anything that I have been able to figure out ... >>>>>> >>>>>> It would certainly help if you included the definition of BookTypes >>>>>> and the INSERT statement you are using. >>>>>> >>>>>>> INSERT INTO BookTypes >>>>>>> SELECT NewID() as TypeID, TypeDesc >>>>>>> FROM inserted >>>>>> >>>>>> A general comment on style here: always provide a column list for your >>>>>> INSERT statements. If another column is added to the table, this INSERT >>>>>> statement will fail. >>>>>> >>>>>> By the way, why not simply use NEWID() as the default value for TypeID? >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se >>>>>> >>>>>> Books Online for SQL Server 2005 at >>>>>> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx >>>>>> Books Online for SQL Server 2000 at >>>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>> >>>>> >>>>> >>>> >>>> >>>> >>> >>> >> > >
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
| home · blog · groups | Questions? Comments? Contact the dn |