Обсуждение: A trigger question
Hi, I'm trying to set up a trigger to prevent any duplicate entries into my table. But I couldn't figure out how to make my trigger work. I have a PostgreSQL 8.1.5 installation. I created the following table: CREATE TABLE attendance ( attendance_id serial, service_date timestamp DEFAULT NOW(), service_type varchar(18), attendance_count integer, CONSTRAINT attendance_pk primary key (attendance_id) ); INSERT INTO attendance (service_date, service_type, attendance_count ) VALUES ('2006-05-01', 'First Service', 100); INSERT INTO attendance (service_date, service_type, attendance_count ) VALUES ('2006-05-01', 'Second Service', 120); Now I create the following: CREATE FUNCTION history_check_fn() RETURNS trigger AS $history_check_fn$ BEGIN -- Check that service_date and service_type are given IF NEW.service_date == OLD.service_date AND NEW.service_type == OLD.service_type THEN RAISE EXCEPTION 'Duplicate entry'; END IF; RETURN; END; $history_check_fn$ LANGUAGE plpgsql; CREATE TRIGGER history_check_tr BEFORE INSERT OR UPDATE ON attendance FOR EACH ROW EXECUTE PROCEDURE history_check_fn(); When I tried to insert a duplicate entry [same service_date and service_type], I got the following error: INSERT INTO attendance (service_date, service_type, attendance_count ) VALUES ('2005-05-01', 'Second Service', 10); ERROR: record "old" is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function "history_check_fn" line 3 at if I couldn't figure out what I'm doing incorrectly. I appreciate if anyone can give me some advices. Thanks, - Chansup
Chansup Byun wrote: > Hi, > > I'm trying to set up a trigger to prevent any duplicate entries into > my table. But I couldn't figure out how to make my trigger work. > > I have a PostgreSQL 8.1.5 installation. > I created the following table: > > CREATE TABLE attendance ( > attendance_id serial, > service_date timestamp DEFAULT NOW(), > service_type varchar(18), > attendance_count integer, > CONSTRAINT attendance_pk primary key (attendance_id) > ); > > INSERT INTO attendance (service_date, service_type, attendance_count ) > VALUES ('2006-05-01', 'First Service', 100); > INSERT INTO attendance (service_date, service_type, attendance_count ) > VALUES ('2006-05-01', 'Second Service', 120); > > Now I create the following: > > CREATE FUNCTION history_check_fn() RETURNS trigger AS $history_check_fn$ > BEGIN > -- Check that service_date and service_type are given > IF NEW.service_date == OLD.service_date AND > NEW.service_type == OLD.service_type THEN > RAISE EXCEPTION 'Duplicate entry'; > END IF; > RETURN; > END; > $history_check_fn$ LANGUAGE plpgsql; > > CREATE TRIGGER history_check_tr > BEFORE INSERT OR UPDATE ON attendance > FOR EACH ROW EXECUTE PROCEDURE history_check_fn(); > > When I tried to insert a duplicate entry [same service_date and > service_type], I got the following error: > > INSERT INTO attendance (service_date, service_type, attendance_count ) > VALUES ('2005-05-01', 'Second Service', 10); > ERROR: record "old" is not assigned yet > DETAIL: The tuple structure of a not-yet-assigned record is > indeterminate. > CONTEXT: PL/pgSQL function "history_check_fn" line 3 at if > > I couldn't figure out what I'm doing incorrectly. > I appreciate if anyone can give me some advices. > I don't think OLD gets assigned for inserts. You should use TG_OP to determine whether it is an insert or update calling the trigger. http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html However, you might simply be better off defining some unique indexes to prevent duplicates. -Jonathan
On Mon, Dec 18, 2006 at 22:18:43 -0500, Chansup Byun <byun1067@gmail.com> wrote: > Hi, > > I'm trying to set up a trigger to prevent any duplicate entries into > my table. But I couldn't figure out how to make my trigger work. Couldn't you use a unique constraint to do this?
On 12/20/06, Bruno Wolff III <bruno@wolff.to> wrote: > On Mon, Dec 18, 2006 at 22:18:43 -0500, > Chansup Byun <byun1067@gmail.com> wrote: > > Hi, > > > > I'm trying to set up a trigger to prevent any duplicate entries into > > my table. But I couldn't figure out how to make my trigger work. > > Couldn't you use a unique constraint to do this? > Yes, I found that from a private email. it works great. Thanks, - Chansup