Situation is as below. I have a table like here:
Col1 Col2 Col3 Col4 Col5 Col6
a b c 1 e 1
a b c 3 l 1
a b c 1 e 0
a b f 1 f 1
The idea is that I cant update existing data or add new row which has combination a b c 1 ? 1. I have to block adding 1 in last column if there is already some combination of cols 1-3开发者_C百科, but I can still may add same combination with 0 in col 6.
For more complex logic, you use triggers: trigger gets triggered at specified time (insert, update, delete) - before (use word "for") or after.
Your example - trigger that does not allow inserting given combination of columns:
create table MyTable (
Col1 char, Col2 char, Col3 char, Col4 int, Col5 char, Col6 int
);
insert into MyTable (Col1, Col2, Col3, Col4, Col5, Col6)
values ('a', 'b', 'c', 1, 'e', '1');
insert into MyTable (Col1, Col2, Col3, Col4, Col5, Col6)
values ('a', 'b', 'f', 1, 'e', '1');
create trigger [dbo].[trigger_MyTable] on MyTable for insert as
begin
if 0 < (select count(*) from inserted where
Col1='a' and Col2='b' and Col3='c' and Col4=1 and Col6=1)
begin
raiserror 50009 'wrong insert, no way!'
rollback transaction
return
end
end;
-- now it fails
insert into MyTable (Col1, Col2, Col3, Col4, Col5, Col6)
values ('a', 'b', 'c', 1, 'e', '1');
Of course, you can create trigger for update (use "inserted" table), or for delete (use "deleted" table). You might do other actions (like inserting another row in another table), ...
Give a MSDN try as well: http://msdn.microsoft.com/en-us/library/ms189799(v=SQL.90).aspx
Unless I misunderstood you requirements I think you want a unique constraint across all columns except col5.
Alter Table Foo
ADD CONSTRAINT ak_orf UNIQUE
(Col1, Col2 ,Col3 ,Col4 ,Col6),
精彩评论