Hello
I have 2 tables and I must make asure unique values like this.
table1
id
nr - integer
table2
id
id_table1 - FK in Table 1
valid_from - timestamp
There must be unique values for:
- nr - from table1
and
- YEAR(MIN(valid_from)) from table 2
I already made this with a function but in rare cases the function
fails: when the insert time is very close for 2 inserts the check will
fail and I will have 2 numbers on the same year.
How should I fix this?
This is the function:
CREATE OR REPLACE FUNCTION table2_check_uni_func() RETURNS trigger AS
$BODY$
DECLARE
currenr_nr_fo integer;
current_id integer;
BEGIN
IF( (SELECT id FROM table2 WHERE id_table1=NEW.id_table1 ORDER BY
valid_from ASC LIMIT 1) = NEW.id ) THEN /*IF the first valid from is
edited*/
SELECT INTO currenr_nr_fo f.nrfo FROM table1 f WHERE f.id =
NEW.id_table1;
IF( (SELECT count(*) as nr
FROM table1 f
JOIN table2 fd1 ON (fd1.id_table1 = f.id AND
to_char(fd1.valid_from, 'YYYY')=TO_CHAR(NEW.valid_from, 'YYYY'))
LEFT JOIN table2 fd2 ON (fd2.id_table1 = f.id AND
fd2.valid_from<fd1.valid_from)
WHERE f.nr = currenr_nr_fo AND f.id!=NEW.id_table1 AND
fd2.id IS NULL) > 0 ) THEN RAISE EXCEPTION 'Nr % already used',
currenr_nr_fo ;
ELSE
RETURN NEW;
END IF;
ELSE
RETURN NEW;
END IF;
END;
Thank you.
