Hello All, I have an strange issue regarding inserts via a view. Below are
the environemnts and the code. Both environments are identical however,
service packs a different.
Environment 1: This environmnet the insert works
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Windows 2k sp4
Environment 2: This envirnoment the insert fails
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
Here is the insert statment;
INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
R.RestrictionType,
R.SecAssocType,
R.RestrictionName,
R.CanOverride,
D.TranCodeBits,
D.MinCompRestPercent,
D.MaxCompRestPercent,
D.IsAggregate,
D.RoundOption,
D.RoundFactor,
D.MinShares,
D.MinValue,
D.CompType)
VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+000
,
0.000000000000000e+000, 0)
Here is the code to the view
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER VIEW vMoxyStandardRestriction AS
SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
R.RestrictionName, R.CanOverride,
D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggregate,
D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
FROM MoxyRestriction R, MoxyRestDef D
WHERE R.RestrictionID = D.RestrictionID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThis is the error message in the environment which does not work.
Server: Msg 4405, Level 16, State 2, Line 1
View or function 'vMoxyStandardRestriction' is not updatable because the
modification affects multiple base tables.
"FredG" wrote:
> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000, 0.000000000000000e+0
00,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent, D.IsAggreg
ate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO|||Hi Fred
Your error messages seems pretty explicit. You cannot insert into a view
that is based on a join. If you could, your single insert would have to put
rows into both the underlying tables, and according to BOL:
[If you insert into a view] .. the modifications made by the INSERT
statement cannot affect more than one of the base tables referenced in the
FROM clause of the view. For example, an INSERT into a multitable view must
use a column_list that references only columns from one base table.
If you are not getting the message for one of your servers, that is the
weirdness. Perhaps you have an INSTEAD OF TRIGGER on the view on that
server.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"FredG" <FredG@.discussions.microsoft.com> wrote in message
news:B1BE7DA5-30D1-41F7-8C63-E213DA147DFA@.microsoft.com...
> Hello All, I have an strange issue regarding inserts via a view. Below are
> the environemnts and the code. Both environments are identical however,
> service packs a different.
> Environment 1: This environmnet the insert works
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Windows 2k sp4
> Environment 2: This envirnoment the insert fails
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
> Here is the insert statment;
> INSERT INTO vMoxyStandardRestriction ( R.RestrictionID,
> R.RestrictionType,
> R.SecAssocType,
> R.RestrictionName,
> R.CanOverride,
> D.TranCodeBits,
> D.MinCompRestPercent,
> D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption,
> D.RoundFactor,
> D.MinShares,
> D.MinValue,
> D.CompType)
> VALUES (11, 1, 16, 'Unreconciled', 1, 12, 0.000000000000000e+000,
> 0.000000000000000e+000, 0, 0, 0.000000000000000e+000,
> 0.000000000000000e+000,
> 0.000000000000000e+000, 0)
> Here is the code to the view
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS OFF
> GO
> ALTER VIEW vMoxyStandardRestriction AS
> SELECT R.RestrictionID, R.RestrictionType, R.SecAssocType,
> R.RestrictionName, R.CanOverride,
> D.TranCodeBits, D.MinCompRestPercent, D.MaxCompRestPercent,
> D.IsAggregate,
> D.RoundOption, D.RoundFactor, D.MinShares, D.MinValue, D.CompType
> FROM MoxyRestriction R, MoxyRestDef D
> WHERE R.RestrictionID = D.RestrictionID
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Wednesday, March 7, 2012
Friday, February 24, 2012
Inserting records using the details view or programmatically
I'm a new user to vwd.
If I use a details view control on my page, I have noticed that the "New" link is not visible unless there is at least one record in the table. Is there any way of making it visible where there aren't any records?
My web pages are currently hosted at vwdhosting. I have uploaded my database with the record structure onto the web site and I am using a remote connection string to access it. I have had users updating data in another table on the remote database. If I add records to my new table locally and upload the database to the remote site, all the data that my users have been adding will be lost.
So, if I can't add my first record using a control on my web page when there are no records in the table, should I be doing it programmatically? If so, how?
Thanks,
Julie
Use the empty template. Add a button, that has a commandname of "New".
I usually use something like:
No records found, <asp:button ...>Add a new record</asp:button>
as the empty template.
|||Fabulous, thank you.Sunday, February 19, 2012
Inserting or Updating a View
In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View?
For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
Woof has Columns WoofID(key) and WoofName(nvarchar).
SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
I make a view:
SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
dbo.zSonOfWoof.SonOfWoofName,
dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
dbo.zGrandSonOfWoof.zGrandSonOfWoofName
FROM dbo.zWoof INNER JOIN
dbo.zSonOfWoof ON dbo.zWoof.WoofID =
dbo.zSonOfWoof.WoofID INNER JOIN
dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
dbo.zGrandSonOfWoof.zSonOfWoofID
But I can not find a way to use a Stored procedure to insert a column to
GrandsonOfWoof, for example.
Any recommendation would be greatly appreciated.. (Do I use an "Indexed
View"?)
TIA,
Paul
(woof!)On Thu, 26 Jan 2006 10:32:09 -0500, Paul wrote:
>In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View
?
>For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
>Woof has Columns WoofID(key) and WoofName(nvarchar).
>SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
>GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
>GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
>I make a view:
>SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID
,
>dbo.zSonOfWoof.SonOfWoofName,
> dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
>dbo.zGrandSonOfWoof.zGrandSonOfWoofName
>FROM dbo.zWoof INNER JOIN
> dbo.zSonOfWoof ON dbo.zWoof.WoofID =
>dbo.zSonOfWoof.WoofID INNER JOIN
> dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
>dbo.zGrandSonOfWoof.zSonOfWoofID
>But I can not find a way to use a Stored procedure to insert a column to
>GrandsonOfWoof, for example.
>Any recommendation would be greatly appreciated.. (Do I use an "Indexed
>View"?)
>TIA,
>Paul
>(woof!)
>
Hi Paul (meow),
You can't insert rows in the tables through this view. Since the view
shows data from three tables, an INSERT might be equivalent to an INSERT
in all three base tables - and that is not supported.
If you can just insert into the base tables, do so.
If you really *have* to insert through this view, then check out INSTEAD
OF triggers. They are described in Books Online. If the help there is
not enough to get you going, then by all means come back here for more
help - but in that case, I'll have to ask you to use CREATE TABLE and
INSERT statements to explain your situation. See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP
For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
Woof has Columns WoofID(key) and WoofName(nvarchar).
SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
I make a view:
SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID,
dbo.zSonOfWoof.SonOfWoofName,
dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
dbo.zGrandSonOfWoof.zGrandSonOfWoofName
FROM dbo.zWoof INNER JOIN
dbo.zSonOfWoof ON dbo.zWoof.WoofID =
dbo.zSonOfWoof.WoofID INNER JOIN
dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
dbo.zGrandSonOfWoof.zSonOfWoofID
But I can not find a way to use a Stored procedure to insert a column to
GrandsonOfWoof, for example.
Any recommendation would be greatly appreciated.. (Do I use an "Indexed
View"?)
TIA,
Paul
(woof!)On Thu, 26 Jan 2006 10:32:09 -0500, Paul wrote:
>In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View
?
>For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof.
>Woof has Columns WoofID(key) and WoofName(nvarchar).
>SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName.
>GrandsonOfWoof has Cols GrandsonOfWoofID, SonOfWoofID, and
>GrandSonOfWoofName. (with z's prefixed, etc... ( just like dogs.))
>I make a view:
>SELECT dbo.zWoof.WoofID, dbo.zWoof.WoofName, dbo.zSonOfWoof.SonOfWoofID
,
>dbo.zSonOfWoof.SonOfWoofName,
> dbo.zGrandSonOfWoof.zGrandsonOfWoofID,
>dbo.zGrandSonOfWoof.zGrandSonOfWoofName
>FROM dbo.zWoof INNER JOIN
> dbo.zSonOfWoof ON dbo.zWoof.WoofID =
>dbo.zSonOfWoof.WoofID INNER JOIN
> dbo.zGrandSonOfWoof ON dbo.zSonOfWoof.SonOfWoofID =
>dbo.zGrandSonOfWoof.zSonOfWoofID
>But I can not find a way to use a Stored procedure to insert a column to
>GrandsonOfWoof, for example.
>Any recommendation would be greatly appreciated.. (Do I use an "Indexed
>View"?)
>TIA,
>Paul
>(woof!)
>
Hi Paul (meow),
You can't insert rows in the tables through this view. Since the view
shows data from three tables, an INSERT might be equivalent to an INSERT
in all three base tables - and that is not supported.
If you can just insert into the base tables, do so.
If you really *have* to insert through this view, then check out INSTEAD
OF triggers. They are described in Books Online. If the help there is
not enough to get you going, then by all means come back here for more
help - but in that case, I'll have to ask you to use CREATE TABLE and
INSERT statements to explain your situation. See www.aspfaq.com/5006.
Hugo Kornelis, SQL Server MVP
Inserting on Partitioned View
Hello, I'm trying to insert on a partitioned view and I get this error:
INSERT INTO dbo.TREP_NOVEDADES
( NMSEC_NOVEDAD,
CDCONCEPTO,
CDTIPO_VALOR,
CDPRECIO,
CDTIP_HECT_DTO_PAG,
FEGENERACION,
FEPAGO_COBRO,
CDMETODO_DISTRIBUC,
CDTIPO_DCTO_PAGO,
VRDESCUENTO_PAGO,
SNTERCERO,
CDCLASE_NOVEDAD,
CDENTIDAD,
CDESTADO,
CDCAUSA,
NMSEC_PREFIJO,
SNPAGO_CAJA,
SNVAL_PROPOR_PESO,
CDTIP_HECT_DISTRIB,
CDMONEDA,
CDCOMPANIA,
NMSEC_NOVEDAD_PADR,
SNGENERADA,
POEMBARGO,
CDTERCERO,
CDCIA_TERCERO,
NMSEM_GENERACION,
NMANO_GENERACION,
NMPER_GENERACION,
NMSEM_PAGOCOBRO,
NMANO_PAGOCOBRO,
NMPER_PAGOCOBRO,
CDTERCERO_CAJA,
CDSUCURSAL_CAJA,
CDCOMPANIA_CAJA)
VALUES ( 3904,
'C01',
'V',
null,
null,
GETDATE(),
GETDATE(),
null,
null,
25999,
'N',
'F',
'016',
'01',
'000001',
null,
'N',
'N',
null,
'PESOC',
'01',
null,
'N',
null,
null,
null,
null,
2007,
null,
null,
null,
null,
null,
null,
null)
Server: Msg 4436, Level 16, State 12, Line 1
UNION ALL view 'TREP_NOVEDADES' is not updatable because a partitioning column was not found.
-
This is the tables information:
CREATE TABLE [dbo].[TREP_NOVEDADES_2006] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TREP_NOVEDADES_2006] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN2006] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2006_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2006_NMANOGENER_CK] CHECK ([NMANO_GENERACION] <= 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2006_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2006_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2006_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2006_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
GO
CREATE TABLE [dbo].[TREP_NOVEDADES_2007] (
[NMSEC_NOVEDAD] [numeric](12, 0) NOT NULL ,
[CDCONCEPTO] [varchar] (12) NOT NULL ,
[CDTIPO_VALOR] [char] (1) NULL ,
[CDPRECIO] [varchar] (12) NULL ,
[CDTIP_HECT_DTO_PAG] [varchar] (2) NULL ,
[FEGENERACION] [datetime] NOT NULL ,
[FEPAGO_COBRO] [datetime] NULL ,
[CDMETODO_DISTRIBUC] [char] (1) NULL ,
[CDTIPO_DCTO_PAGO] [char] (1) NULL ,
[VRDESCUENTO_PAGO] [numeric](18, 5) NULL ,
[SNTERCERO] [char] (1) NOT NULL ,
[CDCLASE_NOVEDAD] [char] (1) NOT NULL ,
[CDENTIDAD] [varchar] (3) NOT NULL ,
[CDESTADO] [varchar] (2) NOT NULL ,
[CDCAUSA] [varchar] (6) NOT NULL ,
[NMSEC_PREFIJO] [numeric](12, 0) NULL ,
[SNPAGO_CAJA] [char] (1) NOT NULL ,
[SNVAL_PROPOR_PESO] [char] (1) NOT NULL ,
[CDTIP_HECT_DISTRIB] [varchar] (2) NULL ,
[CDMONEDA] [varchar] (5) NOT NULL ,
[CDCOMPANIA] [varchar] (2) NOT NULL ,
[NMSEC_NOVEDAD_PADR] [numeric](12, 0) NULL ,
[SNGENERADA] [char] (1) NOT NULL ,
[POEMBARGO] [decimal](7, 4) NULL ,
[CDTERCERO] [varchar] (15) NULL ,
[CDCIA_TERCERO] [varchar] (2) NULL ,
[NMSEM_GENERACION] [tinyint] NULL ,
[NMANO_GENERACION] [smallint] NULL ,
[NMPER_GENERACION] [numeric](6, 0) NULL ,
[NMSEM_PAGOCOBRO] [tinyint] NULL ,
[NMANO_PAGOCOBRO] [smallint] NULL ,
[NMPER_PAGOCOBRO] [numeric](6, 0) NULL ,
[CDTERCERO_CAJA] [varchar] (15) NULL ,
[CDSUCURSAL_CAJA] [varchar] (4) NULL ,
[CDCOMPANIA_CAJA] [varchar] (2) NULL ,
[SNGENERAR_TRG] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TREP_NOVEDADES_2007] ADD
CONSTRAINT [DF__TREP_NOVE__SNGEN] DEFAULT ('S') FOR [SNGENERAR_TRG],
CONSTRAINT [TREP_NOVEDADES2007_PK] PRIMARY KEY CLUSTERED
(
[NMSEC_NOVEDAD]
) ON [PRIMARY] ,
CONSTRAINT [TREP_NOVEDADES_2007_NMANOGENER_CK] CHECK ([NMANO_GENERACION] > 2006),
CONSTRAINT [TREP_NOVEDADES_CDMET_DIST2007_CK] CHECK ([CDMETODO_DISTRIBUC] is null or ([CDMETODO_DISTRIBUC] = 'P' or ([CDMETODO_DISTRIBUC] = 'I' or ([CDMETODO_DISTRIBUC] = 'H' or ([CDMETODO_DISTRIBUC] = 'C' or [CDMETODO_DISTRIBUC] = 'D'))))),
CONSTRAINT [TREP_NOVEDADES_CDTIP_DCTO2007_CK] CHECK ([CDTIPO_DCTO_PAGO] is null or ([CDTIPO_DCTO_PAGO] = 'H' or ([CDTIPO_DCTO_PAGO] = 'C' or [CDTIPO_DCTO_PAGO] = 'A'))),
CONSTRAINT [TREP_NOVEDADES_CDTIPO_VAL2007_CK] CHECK ([CDTIPO_VALOR] is null or ([CDTIPO_VALOR] = 'P' or [CDTIPO_VALOR] = 'V' or [CDTIPO_VALOR] = 'E')),
CONSTRAINT [TREP_NOVEDADES_SNGENERAD2007A_CK] CHECK ([SNGENERADA] = 'S' or [SNGENERADA] = 'N')
go
CREATE VIEW TREP_NOVEDADES
AS
SELECT * FROM dbo.TREP_NOVEDADES_2006
UNION ALL
SELECT * FROM dbo.TREP_NOVEDADES_2007
My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.
Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.
Chris
|||Chris Howarth wrote:
My first instinct would be to create an InsteadOf trigger on the view which would include logic to determine which base table each row of data should be inserted into. Obviously you'd have to update the trigger's definition along with the view's when you come to add new tables.
Like I said, this is an initial reaction - I'm not sure if there's a more efficient way of handling this.
Chris
Hello, I think It's possible to do an UPDATE or INSERT into the view and I'll get my row modified or inserted into the correct base table, depending of the CHECK condition I created before on every base table without creating any trigger...
Isn't It possible..? Do I necessarily have to create the trigger ?
Thanks|||
I think you're right - check out the 'Updatable Partitioned Views' section in the page at this link:
http://msdn2.microsoft.com/en-us/library/aa933141(SQL.80).aspx
There are several criteria that have to be met for INSERTs/UPDATEs/DELETEs to function correctly against this type of View - one of which is that the partitioning column must be part of each of the tables' Primary Key.
If you can't meet the requirements laid out in the above document then your next best option would be to go down the INSTEAD OF TRIGGER route.
Chris
Subscribe to:
Posts (Atom)