Informix 4GL and triggers

Question

I want a simple SQL (trigger) for duplicate checking. My table name is test1 with 2 columns, code and sname. Before inserting a new record, check if the record already exists: if it does, generate an error and do not insert; if it does not, let the insert proceed.

How do I do that?

Answer 1

The simplest, most reliable way to ensure that there is no duplicate data in the table is not using triggers at all, but using UNIQUE or PRIMARY KEY constraints:

CREATE TABLE test1
(
    code    INTEGER NOT NULL PRIMARY KEY,
    sname   VARCHAR(32) NOT NULL UNIQUE
);

The four constraints (two NOT NULL, one PRIMARY KEY, one UNIQUE) automatically ensure that no duplicate records are inserted into the table.

If you choose to add a trigger, it will be duplicating the work that is done by these constraints.

As to how to do it, you will need to create a stored procedure which is invoked from the trigger statement. It will be given the new code and new name, and will do a SELECT to see whether any matching record occurs, and will raise an exception if it does and will not raise an exception if not.

CREATE PROCEDURE trig_insert_test1(ncode INTEGER, nname VARCHAR(32))
    DEFINE ocode INTEGER;
    FOREACH SELECT code INTO ocode
              FROM test1
             WHERE code = ncode OR sname = nname
        RAISE EXCEPTION -271, -100, "Value to be inserted already exists in table test1";
    END FOREACH;
END PROCEDURE

Then you use:

CREATE TRIGGER ins_test1 INSERT ON test1
    REFERENCING NEW AS NEW
    FOR EACH ROW (EXECUTE PROCEDURE ins_trig_test1(new.code, new.sname))

In Informix 4GL, you can either create strings containing these statements, and then PREPARE and EXECUTE (and FREE) them, or you can use SQL blocks:

SQL
    CREATE TRIGGER ins_test1 INSERT ON test1
        REFERENCING NEW AS NEW
        FOR EACH ROW (EXECUTE PROCEDURE ins_trig_test1(new.code, new.sname))
END SQL

But, as I said at the outset, using triggers for this is not the best way to go; it is redundant given the table definition.

I've not run any of the SQL or SPL past the server; you'll need to check that the semi-colons are in the right places in the SPL, as SPL is fussy about that.

You can find the syntax for the SQL and SPL statements in the Informix 11.70 Information Centre.