UNION ALL 视图 不可更新 因为其定义中包含不允许的构造 ##请高手解答
CREATE TABLE [SFCDAA08040903] ( [AUDITING] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [COMPANYID] [varchar] (15) COLLATE Chinese_PRC_BIN NULL , [CREATEHOST] [varchar] (30) COLLATE Chinese_PRC_BIN NULL , [CREATETIME] [varchar] (14) COLLATE Chinese_PRC_BIN NULL , [CREATEUSER] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [MODIFYHOST] [varchar] (30) COLLATE Chinese_PRC_BIN NULL , [MODIFYTIME] [varchar] (14) COLLATE Chinese_PRC_BIN NULL , [MODIFYUSER] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [SIGN] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [CHECKOUT] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [DAA001] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA002] [varchar] (4) COLLATE Chinese_PRC_BIN NOT NULL , [DAA003] [varchar] (11) COLLATE Chinese_PRC_BIN NOT NULL , [DAA004] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA005] [varchar] (2) COLLATE Chinese_PRC_BIN NOT NULL , [DAA006] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA007] [varchar] (25) COLLATE Chinese_PRC_BIN NOT NULL , [DAA008] [varchar] (30) COLLATE Chinese_PRC_BIN NOT NULL , [DAA009] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA010] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA011] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA012] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [DAA013] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [DAA014] [varchar] (50) COLLATE Chinese_PRC_BIN NULL , [DAA015] [varchar] (2) COLLATE Chinese_PRC_BIN NULL , [DAA016] [varchar] (20) COLLATE Chinese_PRC_BIN NULL , [SECURITYID] [varchar] (36) COLLATE Chinese_PRC_BIN NULL , CONSTRAINT [PK_SFCDAA080409031] PRIMARY KEY CLUSTERED ( [DAA001], [DAA002], [DAA003] ) ON [EB_shintech_Group] , CONSTRAINT [CK_SFCDAA08040903] CHECK ([DAA003] >= '20080400001' and [DAA003] < '20090400001')) ON [EB_shintech_Group]GOCREATE TABLE [SFCDAA09041003] ( [AUDITING] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [COMPANYID] [varchar] (15) COLLATE Chinese_PRC_BIN NULL , [CREATEHOST] [varchar] (30) COLLATE Chinese_PRC_BIN NULL , [CREATETIME] [varchar] (14) COLLATE Chinese_PRC_BIN NULL , [CREATEUSER] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [MODIFYHOST] [varchar] (30) COLLATE Chinese_PRC_BIN NULL , [MODIFYTIME] [varchar] (14) COLLATE Chinese_PRC_BIN NULL , [MODIFYUSER] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [SIGN] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [CHECKOUT] [varchar] (1) COLLATE Chinese_PRC_BIN NOT NULL , [DAA001] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA002] [varchar] (4) COLLATE Chinese_PRC_BIN NOT NULL , [DAA003] [varchar] (11) COLLATE Chinese_PRC_BIN NOT NULL , [DAA004] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA005] [varchar] (2) COLLATE Chinese_PRC_BIN NOT NULL , [DAA006] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA007] [varchar] (25) COLLATE Chinese_PRC_BIN NOT NULL , [DAA008] [varchar] (30) COLLATE Chinese_PRC_BIN NOT NULL , [DAA009] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA010] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA011] [varchar] (10) COLLATE Chinese_PRC_BIN NOT NULL , [DAA012] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [DAA013] [varchar] (10) COLLATE Chinese_PRC_BIN NULL , [DAA014] [varchar] (50) COLLATE Chinese_PRC_BIN NULL , [DAA015] [varchar] (2) COLLATE Chinese_PRC_BIN NULL , [DAA016] [varchar] (20) COLLATE Chinese_PRC_BIN NULL , [SECURITYID] [varchar] (36) COLLATE Chinese_PRC_BIN NULL , CONSTRAINT [PK_SFCDAA090410031] PRIMARY KEY CLUSTERED ( [DAA001], [DAA002], [DAA003] ) ON [EB_shintech_Group] , CONSTRAINT [CK_SFCDAA09041003] CHECK ([DAA003] >= '20090400001' and [DAA003] < '20100400001')) ON [EB_shintech_Group]GO
CREATE VIEW SFCDAAASSELECT *FROM SFCDAA08040903
CREATE VIEW SFCDAAASSELECT *FROM SFCDAA08040903UNION ALLSELECT *FROM SFCDAA09041003
但是插入数据的时候提示： UNION ALL 视图 SFCDAA 不可更新 因为其定义中包含不允许的构造 当然不能直接插入数据了，不知道往哪个不表里插啊！通常是用触发器来完成数据的插入。
[解决办法]
INSTEAD OF 触发器的主要优点是使不可被修改的视图能够支持修改其中典型的例子是分割视图partitioned view 为了提高查询性能分割视图通常是一个来自多个表的结果集但是也正因此而不支持视图更新下面的例子说明了如何使用INSTEAD OF触发器来支持对分割视图所引用的基本表的修改例12-10 首先创建三个表salemay salejune 和salejuly 这三个表分别用来保存五六七月的销售量信息create table salemaysale_id char 6 not null,sale_name varchar 20 ,sale_qua smallint表salejune 和表salejuly 与salemay 具有相同的数据列创建分割视图saleviewcreate view saleviewasselect * from salemayunion allselect * from salejuneunion allselect * from salejuly在视图saleview 上创建INSTEAD OF 触发器saleviewtrcreate trigger saleviewtr on saleviewinstead of insertasbegindeclare @sale_id char 4select @sale_id=sale_idfrom insertedif substring @sale_id,1,3 ='may'begininsert into salemayselect sale_id, sale_name, sale_quafrom insertedendif substring @sale_id,1,3 ='jun'begininsert into salejuneselect sale_id, sale_name, sale_quafrom insertedendif substring @sale_id,1,3 ='jul'begininsert into salejulyselect sale_id, sale_name, sale_quafrom insertedendend此时能够成功执行插入语句 insert into saleview values ‘jul001’,’先科VCD’,200INSTEAD OF 触发器的另外的优点是通过使用逻辑语句以执行批处理的某一部分而放弃执行其余部分比如可以定义触发器在遇到某一错误时转而执行触发器的另外部分
[解决办法]
INSERT SFCDAA(AUDITING, COMPANYID, CREATEHOST, CREATETIME, CREATEUSER, MODIFYHOST, MODIFYTIME, MODIFYUSER, SIGN, CHECKOUT, DAA001, DAA002, DAA003, DAA004, DAA005, DAA006, DAA007, DAA008, DAA009, DAA010, DAA011, DAA012, DAA013, DAA014, DAA015, DAA016, SECURITYID)select1, 1, 1, 1, 1, 1, 1,1, 1, 1, 1, 2, '20080400005', 4, 5,6, 7, 8, 9, 0, 1, 2, 3, 1, 5, 1, 1
[解决办法]
移走之前，确定你原来的表里对 daa003 的check约束 是正确的。
即两个表的check约束范围不能有重合部分。
