Hi all and thanks for your advice.
Expense(SupplierID(Foreign Key), DocumentID(vchar))
I understand how to add a simple unique constraint on two columns. However, if DocumentID = ‘NA’, I would like to ignore the rules of the constraint.
Some suppliers in our system do not provide an invoice id, for example. Therefore, I leave the field NULL. I would like to remove all nulls for the field ‘DocumentID’ to avoid accounting for the NULLS in my client code.
I am new to SQL Server, but I could figure out how to do this using a trigger. The reason I’m asking here is to see if there is a better way to respond to this scenario or to avoid it by a different design.
Go to Source
Author: Tom Schreiner
I’m not sure it’s possible to do this with a rule, but what I want to do is, when you insert a record:
- If it doesn’t exist, just insert it as usual.
- If it exists, but each field is equal to what you’re inserting, then do nothing.
- If it exists, but any field differs from what you’re inserting, then delete the old record and insert the new one.
The reason for this is that, in the last case, I want to use the cascade deletion productively. That is, if a record has changed, then everything that refers to it is now void and should be removed.
Let’s say my table is:
create table foo (
id integer primary key,
My first attempt was to just delete the old record regardless (before checking the difference):
create rule refresh as
on insert to foo
do instead (
delete from foo where id = new.id;
insert into foo values (new.id, new.foo, new.bar));
This doesn’t work; it’s an infinite loop.
Is it possible to do what I’m after with rules, or would I need a full-blown trigger?
Go to Source