Trigger Help

[kburrows]

Hi all,

I don't know if this possible, but I need to create a trigger that will stop the insert or update on a table when a certain combination already exists:

Column A: Can be the same
Column B: Can be the same
Column C: Can be the same
Column D: - Can either be a 1 or a 0

The first three columns can be duplicate, the fourth column can be a 1 or 0. We allow multiple 0's and only one 1. So, combinations can be:

A B C 1 - ok, if it doesn't already exist.
A B C 0 - ok
A B C 0 - ok
A B C 1 - should fail everytime, already exists because of previous
A B C 0 - ok
A B C 1 - fails again
A B C 0 - ok

Also would like a little message for the failed attempts to be returned.

Any ideas?
Thanks

[TomazZ]

Idea:
Add a column 'seq' to a table and create a sequence.
In trigger:
if D equals 0 fill 'seq' with sequence.nextval
if D equals 1 fill 'seq' with null
Create unique index on (a,b,c,d,seq).

There is of course storage space overhead to think about (if it is big table).