sybase数据库中可以建试图含union,可以对试图select,但是update时报错,请问这个怎么解决?
谢谢
Msg 4427, Level 16, State 1
View 'SPCES' cannot be updated since the view definition contains a UNION operator.
sybase union update
谢谢
Msg 4427, Level 16, State 1
View 'SPCES' cannot be updated since the view definition contains a UNION operator.
sybase union update
你试试建个instead of触发器呢
你是如何来指明,到底是更新那个表的数据,是tba表,还是tbb表呢,那么在你执行update 视图的语句时,这个都可以在触发器中,先进行判断,然后对某个表,或者2个表都进行更新。
呵呵,sql server里面有的,sybase应该也有把
drop table T2--建立2个表
create table t1(id int not null primary key,v varchar(10),tbl varchar(5) not null)create table t2(id int not null primary key,v varchar(10),tbl varchar(5) not null)go
--插入数据
insert into t1
select 1,'aa','01'
insert into t2
select 1 ,'bb','02'
if exists(select * from sys.views where name = 'v_t')
drop view v_t
go
--创建视图
create view v_t
asselect * from t1
union
select * from t2
goif exists(select * from sys.triggers where name = 'trigger_t')
drop trigger dbo.trigger_t
go--1.通过判断区分列tbl的值是'01'或'02',把数据分别插入t1或t2
create trigger dbo.trigger_t
on dbo.v_t
instead of update
asdeclare @t varchar(8);
set @t = '';
if @t = '01'
begin
delete from dbo.t1
from deleted d where t1.id = d.id insert into dbo.t1(id,v,tbl)
select *
from inserted i
select 1
end
else
begin
delete from dbo.t2
from deleted d where t2.id = d.id insert into dbo.t2(id,v,tbl)
select *
from inserted i
select 2
end
go--truncate table t1--1.测试
update v_t
set v = 'aa'
where id = 1
select *
from dbo.t2