1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51
|
drop view v_test1;
drop view v_test2;
drop table exclude;
drop table test1;
create table test1
(
id int not null identity primary key,
filtre bit not null,
val int not null
);
create table exclude (id int not null primary key references test1(id));
insert into test1 (filtre, val) values (0, 1);
insert into test1 (filtre, val) values (1, 2);
insert into test1 (filtre, val) values (1, 3);
insert into test1 (filtre, val) values (0, 4);
insert into test1 (filtre, val) values (1, 5);
insert into test1 (filtre, val) values (0, 6);
go
create view v_test1 as select id, val from test1 where filtre = 0;
go
create view v_test2 as select id, val from test1 where id not in (select id from exclude);
go
select * from v_test1;
delete v_test1 where val > 5;
select * from v_test1;
go
create trigger trg_test1 on v_test2
instead of delete
as
begin
insert into exclude (id) select id from deleted;
end;
go
select * from v_test2;
delete v_test2 where val > 3;
select * from v_test2;
select * from test1;
select * from exclude; |
Partager