June 28, 2006 at 11:43 am
I would appreciate any help I can get.
I am new to instead of triggers, and am trying to do something with instead of triggers and a view that may not be possible.
I have an existing table similar to following:
CREATE TABLE dbo.BigTable
(
[OrderID] [varchar] (31) NOT NULL ,
[TableID] [int] NOT NULL ,
[Col3] [int] NOT NULL ,
[Col4] [varchar] (32) NULL ,
[Path] [char] (255) NULL ,
CONSTRAINT [BigTablePrimaryKey] PRIMARY KEY CLUSTERED
(
[OrderID],
[TableID]
)
)
We have a 3rd party app that accesses and updates this table.
I wish to create two tables and an updateable view to replace this table that will not break the 3rd party app.
I have created the following 2 tables and view:
CREATE TABLE dbo.NewTable
(
[OrderID] [varchar] (31) NOT NULL ,
[TableID] [int] NOT NULL ,
[Col3] [int] NOT NULL ,
[Col4] [varchar] (32) NULL ,
CONSTRAINT [NewTablePrimaryKey] PRIMARY KEY CLUSTERED
(
[OrderID],
[TableID]
)
)
CREATE TABLE dbo.NewTableDetail
(
[OrderID] [varchar] (31) NOT NULL ,
[TableID] [int] NOT NULL ,
[EffectID] [int] NOT NULL ,
[Path] [char] (255) NULL ,
CONSTRAINT [NewTableDetailPrimaryKey] PRIMARY KEY CLUSTERED
(
[OrderID],
[TableID],
[EffectID]
)
)
CREATE VIEW dbo.BigTable
AS
SELECT
nt.OrderID,
nt.TableID,
ntd.EffectID,
nt.Col3,
nt.Col4,
ntd.Path
FROM dbo.NewTable nt
INNER JOIN dbo.NewTableDetail ntd
ON nt.OrderID = ntd.OrderID
AND nt.TableID = ntd.TableID
The goal is to allow us multiple detail records for each BigTable record, with EffectID added as Detail key field.
The original record will always have an EffectID of 0.
I have attempted to write instead of triggers so that when the app attempts to
insert or update the view, it will instead do the command on the new tables.
One of the problems I am having is that I want a default EffectID of 0 to be
used by every query that doesn't specify an EffectID so that existing queries will continue to work.
I have a COALESCE(EffectID, 0) AS EffectID FROM INSERTED in the insert and update triggers,
and a default value of 0 for EffectID in the
detail table, but am still getting an error of cannot insert null in EffectID if an existing query
attempts to insert a record without specifying EffectID.
Is it possible to do what I am trying to do, and if so, could someone please point me in the right direction?
Thanks for any pointers.
June 29, 2006 at 2:32 am
Problem is your view presents NOT NULL column EffectId, so it's NOT NULL by definition.
1st, it's really bad practice to have DEFAULT to replace NULL. Defaults must come only from business rules, not from lack of experience of the person making design.
I would suggest to allow NULL in new table and apply ISNULL(EffectId, 0) in SPs used by application.
2nd, if you decide to keep NOT NULL DEFAULT 0 you may use
NULLIF(EffectId, 0)
or NULLIF(EffectId, -1) (if you still want to return 0 to application as xero, not NULL)
in your view, your column will become nullable, so you may insert NULLs in it.
_____________
Code for TallyGenerator
June 29, 2006 at 3:47 pm
Thanks Sergiy,
I think that NULLIF(EffectId, -1) is going to do the trick.
The business rule actually dictates that the original record added will have an EffectID of 0, but in most cases, that original record will be inserted by the app that we have no control over the existing queries. We want the EffectID of 0 to automatically be applied to the initial insert even though the EffectID field will not be included in the insert query. We will then create copies on the original in apps that we do control that will insert additional detail records with different EffectID.
Thanks again, your help is very much appreciated.
Donnie
June 30, 2006 at 11:30 am
Your default will still work even if the column is nullable. But if you can't allow the column to contain nulls under any circumstances (e.g. mistake in app code), and using 'after' triggers to replace nulls with zeros in the base table is unacceptable, you might have to go down the suggested route. Any transformation that doesn't affect the data values (e.g. +0) can be used, since SQL will then treat the column as an expression, rather than a base table column, and will treat it as nullable. But be warned that transforming columns in this way can cause a performance hit on the view.
You might want to consider adding an indexed calculated column defined as NULLIF(EffectId, -1), (or EffectID + 0, or whatever) on the underlying table, which can be returned in the view in place of the original column (and aliased with the original column name, of course). The 'instead of' trigger can divert values to the original column, when they are supplied.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply