Table not updating a view

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ccmcbride
Posts: 101
Joined: Tue 01 May 2007 16:36

Table not updating a view

Post by ccmcbride » Tue 14 Jul 2015 23:23

my Tables:
bAddress

Code: Select all

CREATE TABLE [dbo].[bAddress](
	[RecUID] [bigint] IDENTITY(1,1) NOT NULL,
	[UID] [varchar](20) NOT NULL,
	[Address1] [varchar](40) NULL DEFAULT (''),
	[Address2] [varchar](40) NULL DEFAULT (''),
	[City] [varchar](30) NULL DEFAULT (''),
	[St] [varchar](2) NULL DEFAULT (''),
	[Zip] [varchar](10) NULL DEFAULT (''),
	[AddressBlock] [varchar](max) NULL,
	[Country] [varchar](40) NULL DEFAULT (''),
	[GEOLat] [numeric](11, 5) NULL CONSTRAINT [DF_bAddress_GEOLat]  DEFAULT ((0)),
	[GEOLong] [numeric](11, 5) NULL CONSTRAINT [DF_bAddress_GEOLong]  DEFAULT ((0)),
	[wResult] [varchar](max) NULL,
	[WhoEdit] [varchar](40) NULL DEFAULT (''),
	[EditDateTime] [datetime] NULL DEFAULT (getdate()),
	[EditCount] [int] NULL DEFAULT ((0)),
	[EditModule] [varchar](30) NULL DEFAULT (''),
	[EditTimeStamp] [timestamp] NOT NULL,
	[LocationID] [varchar](10) NULL DEFAULT (''),
	[EditUID] [varchar](20) NULL DEFAULT (''),
	[sys_isDeleted] [bit] NULL DEFAULT ((0)),
	[sys_DeleteDate] [datetime] NULL,
	[sys_DeletedByUID] [varchar](20) NULL DEFAULT (''),
PRIMARY KEY CLUSTERED 
(
	[RecUID] ASC
)
baddressLink:

Code: Select all

CREATE TABLE [dbo].[bAddressLink](
	[RecUID] [bigint] IDENTITY(1,1) NOT NULL,
	[UID] [varchar](20) NOT NULL,
	[MasterUID] [varchar](20) NULL DEFAULT (''),
	[Name] [varchar](80) NULL,
	[AddressUID] [varchar](20) NULL DEFAULT (''),
	[AddressTypeUID] [varchar](20) NULL DEFAULT (''),
	[isDefault] [bit] NULL DEFAULT ((0)),
	[isActive] [bit] NULL DEFAULT ((0)),
	[WhoEdit] [varchar](40) NULL DEFAULT (''),
	[EditDateTime] [datetime] NULL DEFAULT (getdate()),
	[EditCount] [int] NULL DEFAULT ((0)),
	[EditModule] [varchar](30) NULL DEFAULT (''),
	[EditTimeStamp] [timestamp] NOT NULL,
	[LocationID] [varchar](10) NULL DEFAULT (''),
	[EditUID] [varchar](20) NULL DEFAULT (''),
	[sys_isDeleted] [bit] NULL DEFAULT ((0)),
	[sys_DeleteDate] [datetime] NULL,
	[sys_DeletedByUID] [varchar](20) NULL DEFAULT (''),
PRIMARY KEY CLUSTERED 
(
	[RecUID] ASC
)
bAddressType:

Code: Select all

CREATE TABLE [dbo].[bAddressType](
	[RecUID] [bigint] IDENTITY(1,1) NOT NULL,
	[UID] [varchar](20) NOT NULL,
	[Name] [varchar](40) NULL DEFAULT (''),
	[IsActive] [bit] NULL DEFAULT ((1)),
	[WhoEdit] [varchar](40) NULL DEFAULT (''),
	[EditDateTime] [datetime] NULL DEFAULT (getdate()),
	[EditCount] [int] NULL DEFAULT ((0)),
	[EditModule] [varchar](30) NULL DEFAULT (''),
	[EditTimeStamp] [timestamp] NOT NULL,
	[LocationID] [varchar](10) NULL DEFAULT (''),
	[EditUID] [varchar](20) NULL DEFAULT (''),
	[sys_isDeleted] [bit] NULL DEFAULT ((0)),
	[sys_DeleteDate] [datetime] NULL,
	[sys_DeletedByUID] [varchar](20) NULL DEFAULT (''),
PRIMARY KEY CLUSTERED 
(
	[RecUID] ASC
)
my View:

Code: Select all

CREATE View [dbo].[AddressLink]
With View_MetaData 
 as select t.RecUID, t.UID
, t.MasterUID, t.Name
, t.AddressUID, coalesce(address.Address1, '') as Address1, coalesce(Address.Address2, '') as Address2, coalesce(Address.City, '') as City
, coalesce(Address.St, '') as St, coalesce(Address.Zip, '') as Zip, coalesce(Address.Country, '') as Country, coalesce(Address.AddressBlock, '') as AddressBlock
, t.AddressTypeUID, coalesce(AddressType.Name, '') as AddressType
, t.isDefault, t.isActive
, t.EditUID, coalesce(WhoEdit.Name, '') as WhoEdit
, t.EditCount, t.EditDateTime, t.EditModule, t.EditTimeStamp, t.LocationID
, t.sys_isDeleted, t.sys_DeleteDate, t.sys_DeletedByUID, coalesce(DeletedBy.Name, '') as DeletedBy
from dbo.bAddressLink t
left outer join dbo.AddressType AddressType on AddressType.UID = t.AddressTypeUID and coalesce(AddressType.sys_isdeleted, 'false') = 'false' and coalesce(AddressType.isActive, 'false') = 'true'
left outer join dbo.Address Address on Address.UID = t.AddressUID and coalesce(Address.sys_isdeleted, 'false') = 'false'
left outer join dbo.SMPUser WhoEdit on WhoEdit.UID = t.EditUID and coalesce(WhoEdit.sys_isdeleted, 'false') = 'false'
left outer join dbo.SMPUser DeletedBy on DeletedBy.UID = t.sys_DeletedByUID and coalesce(DeletedBy.sys_isdeleted, 'false') = 'false'
where coalesce(t.sys_isDeleted, 'false') = 'false'
I have trigger code for 'instead of insert/update/delete' on Address Link.
using Delphi 2010, sDac 6.11.23
in my Delphi code, I have a table component, and I am trying to set the address fields on AddressLink, but when it posts, the address fields are skipped.
Normally, great. In this case, not so much, because the view trigger logic will break if the address lines aren't set.
Am I missing something?

azyk
Devart Team
Posts: 1058
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: Table not updating a view

Post by azyk » Thu 16 Jul 2015 14:01

Please provide the script for creating dbo.AddressType (there is only the script for dbo.bAddressType). Since when attempting to execute the script for creating the dbo.AddressLink view, I get the 'Invalid object name 'dbo.AddressType' error.

Post Reply