Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

New to informix

Status
Not open for further replies.

magni

Programmer
Oct 11, 2007
12
CA
Hi,
I want a simple sql (trigger) for duplicate checking. My table name is test1 with 2 columns code and sname, before inserting new record, check - if the record is already exist, then error, not insert. or Insert it.
Thank you, Magni
 
I need help with this following. I created the following procedure and trigger but getting error. This I am using for inofrmix 4GL. Thankyou.

CREATE PROCEDURE trig_insert_test1(ncode CHAR(2), nname CHAR(32))
DEFINE ocode CHAR(2);
FOREACH SELECT code INTO ocode
FROM state
WHERE code = ncode OR sname = nname
RAISE EXCEPTION -271, 100, "Value already exists";
END FOREACH;
END PROCEDURE


create trigger inst_test1 insert on state
referencing new as new
for each row (execute procedure trig_insert_test1(new.code,new.sname));
 
Hi

Would not be better solution to create an unique index? or a primary key?

CREATE UNIQUE INDEX i_test1 ON test1(code);

or

ALTER TABLE test1 ADD CONSTRAINT PRIMARY KEY (code);

Then you´ll get an error on existing row with less effort.

Greetings.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top