How to block wrong inserts and updates into one table?

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),