CODEWITHSUNDEEP
stored-procedurestriggersinformix
cppcoder
cppcoder

Reputation: 23135

Pass concatenated string parameter in informix trigger

I have a trigger which passes some parameters to a procedure and invoke it.

CREATE TRIGGER trig_ins INSERT ON mytable
REFERENCING NEW AS new
FOR EACH ROW(EXECUTE PROCEDURE sp_ins("mytable", new.id, 'I'));

Is there any way I can pass concatenated char as the second parameter to the above procedure call?

I need this because I may need to allow these parameters:

"id = " . new.id
"field1 =" . new.field1 . "and field2 = " . new.field2

Upvotes: 1

Views: 777

Answers (1)

RET
RET

Reputation: 9188

I get the impression this is to produce some sort of shared audit or logging table. You'll obviously need to define the TRIGGER on a per-table basis, so with each declaration, you nominate the relevant fields, e.g.

CREATE TRIGGER trig_ins INSERT ON mytable
REFERENCING NEW AS new
FOR EACH ROW(EXECUTE PROCEDURE sp_ins("mytable",
    "field1 = " || new.field1 || " and field2 = " || new.field2, 'I'));

But it looks and feels a bit clumsy. I can't help thinking this is an XY problem of some sort.

UPDATE

(Following comment 'need not be where_clause')

Well, the obvious answer is to make sure every table has a simple, single surrogate key column, that can be treated like a ROWID.

That's more easily said than done when you have an existing model, of course. If that's not possible, then what you want is a representation of your compound key in a manner that can be parsed later if need be. Exactly how you do that depends on how you intend to parse it: programmatically or through preconstructed SQL fragment as you first proposed. The former is more controllable, but won't produce 'injectable' SQL fragments:

EXECUTE PROCEDURE sp_ins("mytable",
    "<field1>" || NVL(new.field1,"") || "</field1>" ||
    "<field2>" || NVL(new.field2,"") || "</field2>",
    "I");

...is one way of doing it. You can do the SQL fragment approach, it's just the construction is very messy, as shown above. For example, if field1 is a string, the SQL is already broken, and you need to do something like this:

EXECUTE PROCEDURE sp_ins("mytable", 
    "field1 " || NVL('= "'||new.field1||'"','IS NULL') || ' AND ' ||
    "field2 " || NVL('= "'||new.field2||'"','IS NULL'),
    "I")

... and you can be sure that sooner or later you'll run into an Irish problem where a name like O'Malley or Sylvester "Sly" Stallone breaks those embedded quotes. There's no elegant solution because it's an inelegant thing you're trying to do.

Informix provides out-of-the-box audit features, and ways and means of exploring the logical logs. I can't help thinking you'd be better off exploring those.

Upvotes: 2

Related Questions