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?

What have you tried, and why didn't it work? The manuals can be found at the [Informix 11.70 Information Center](http://publib.boulder.ibm.com/infocenter/idshelp/v117/index.jsp). Why do you want a trigger instead of simply creating a unique constraint on each column? Don't code a trigger unless you need to; the non-trigger constraints will be 'more efficient'. And there's a whole separate set of issues related to whether I4GL will recognize the syntax of CREATE TRIGGER; you will probably need to enclose the statement in an SQL CREATE TRIGGER ... END SQL block. — Jonathan Leffler, Aug 11 '12 at 21:17
I have no idea about this informix 4GL. Here they want me to create a trigger, I tried to add by select statement, but no idea. — MJJ3, Aug 13 '12 at 17:05
If it is a classroom exercise, then there might be some merit to it, though the specific constraint is better handled as discussed. If it is for production code, then you need to explain to them that their requirements are not sensible, and you may get them to look at this, and/or discuss it with me (see my profile for email address). — Jonathan Leffler, Aug 13 '12 at 18:09
The easy way to resolve this issue is very simple, if you are using form painter just add the unique key. I spend days for this simple issue. Thank you Jonathan for your support. — MJJ3, Aug 16 '12 at 15:33

Jonathan Leffler · Answer 1 · 2012-08-13T18:10:11.747

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.

You need to explain your setup a little, then. Your database server is either SE or IDS (or, rather unlikely, OnLine or XPS). It may well be on the same machine. Which version of I4GL are you using? (Run `i4gl -V` to find out.) If you don't do any administration of the server, you're probably running SE. You could run `$INFORMIXDIR/lib/sqlexec -V` to confirm that and print the version. If you don't have an `sqlexec` program, you've not got SE; there should then be a `$INFORMIXDIR/bin/oninit` (or possibly `$INFORMIXDIR/bin/tbinit`); you can run that with the `-V` flag to get the version. — Jonathan Leffler, Aug 14 '12 at 13:55
No; I can't help you further because you've not shown the program that's crashing. It needs to be short and sweet and complete. You shouldn't be able to get the program to crash (as in 'dump core'); you might get it stopping because something went wrong. But since I can't see what code you've got, there's no chance of helping you resolve the issue. To add the code, edit the question. — Jonathan Leffler, Aug 15 '12 at 14:56
Glad you managed to resolve the issues. Please remember your duties as the asker of a question — outlined above in a comment to your question. — Jonathan Leffler, Aug 16 '12 at 14:38

Informix 4GL and triggers

1 Answers1