New to informix

[magni]

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

 

[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));

 

[rgomezs]

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.