create table Woo (
woo_pk int identity(1,1) primary key,
name varchar(100) not null,
flags int not null default 0,
)
go
create view Woo__Overflow as
select woo_fk = woo_pk,
bit_0 = cast(flags & 1 as bit),
bit_1 = cast(flags & 2 as bit)
from Woo
go
create trigger Woo__Overflow_I on Woo__Overflow instead of insert as
update w set
flags = i.bit_0 * power(2, 0) |
i.bit_1 * power(2, 1)
from Woo w
inner join inserted i on i.woo_fk = w.woo_pk
go
create trigger Woo__Overflow_U on Woo__Overflow instead of update as
update w set
flags = i.bit_0 * power(2, 0) |
i.bit_1 * power(2, 1)
from Woo w
inner join inserted i on i.woo_fk = w.woo_pk
go
insert Woo (name)
select 'Luke' union all
select 'John' union all
select 'Mike'
update Woo__Overflow set
bit_1 = 1
--inner join Woo w on w.woo_pk = wo.woo_fk
--where w.name = 'Luke'
where woo_fk = 1
update Woo__Overflow set
bit_0 = 1
--inner join Woo w on w.woo_pk = wo.woo_fk
--where w.name = 'John'
where woo_fk = 2
select *
from Woo w
inner join Woo__Overflow wo on w.woo_pk = wo.woo_fk