SQL Server Unique Constraint on two columns with an exception

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.

Thanks!

Go to Source
Author: Tom Schreiner

PostgreSQL rule to delete record before insertion, if the record has changed

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,
  foo integer,
  bar integer
);

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
Author: Xophmeister

Oracle 12c – Compile A Trigger In SqlDeveloper and SqlPlus

When I try to compile a trigger on SqlDeveloper with right click on trigger and select compile, it’s compiling. But when I try to compile a trigger on SqlPlus with this command: alter trigger SCHEMA_NAME.TRIGGER_NAME compile; it’s not compiling but it gives an output about successfully compiling. What is the meaning of this?

Go to Source
Author: jrdba

Create a Postgresql trigger that updates column with PK of other table

Hello and thanks for taking the time in advance to look at my question. This is my first try at using triggers and functions in PostgreSQL (using DBeaver) and I appreciate your feedback.

I’d like to update a column (village_id) every time a new row is entered or updated with the primary key value of a different table (which is not connected as a foreign key directly). See below for an outline of my tables and trigger + function.

Unfortunately, after using the code below I received an error each time I was entering a new row with information. I used an UPDATE statement, but I also considered using an insert into statement, but wasn’t sure what is more preferable. Thank you in advance.

  CREATE TABLE register_table
(register_id integer CONSTRAINT register_id_pk PRIMARY KEY,
  village_id integer NOT NULL
);
      CREATE TABLE er_table
(er_id integer CONSTRAINT er_id_pk PRIMARY KEY,
  register_id integer NOT NULL,
  village_id integer NOT NULL
);

CREATE OR REPLACE FUNCTION update_village_id_column()
    RETURNS trigger AS
  $BODY$
BEGIN
UPDATE schema_example.er_table
SET  village_id = register_table.village_id
from schema_example.register_table
WHERE (schema_example.register_id = new.register_id);

RETURN new; 
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER update_village_id
AFTER INSERT OR UPDATE
ON schema_example.er_table
FOR EACH ROW
EXECUTE PROCEDURE update_village_id_column();

Go to Source
Author: PGerrits