Informix trigger to change inserted values

Question

I would like to change a couple of column values before they get inserted. I am using Informix as database.

I have a table consisting of 3 columns: Name (NVARCHAR), Type (INT), Plan (NVARCHAR).

Every time a new record is inserted, I would like to check the Name value before inserting it. If the Name starts with an F, I would like to set the Type value to 1 and the Plan Name to "Test"

In short, what I want the trigger to do is:

1. For every new insertion, first check if Name value starts with F.
2. If yes, set the Type and Plan to 1 and "Test" then insert.
3. If no, insert the values as-is.

I have looked up the CREATE TRIGGER statement with BEFORE and AFTER. However, I would like to have a clearer example. My case would probably involve BEFORE though.

Answer 1

Here is my version an adaptation of an old example I found in the informix usenet group.

It is possible to update columns in a trigger statement but not very straight forward. You have to use stored procedures an the into statement with the execute procedure command.

It worked here for IBM Informix Dynamic Server Version 12.10.FC11WE.

drop table if exists my_table;
drop sequence if exists my_table_seq;

create table my_table (   
   id                 INTEGER 
                      NOT NULL, 
   col_a              char(32) 
                      NOT NULL, 
   col_b              char(20)  
                      NOT NULL,
   hinweis            char(64),
   uslu               char(12)
                      DEFAULT USER
                      NOT NULL,
   dtlu               DATETIME YEAR TO SECOND
                      DEFAULT CURRENT YEAR TO SECOND
                      NOT NULL
)
;

create sequence my_table_seq 
increment 1
start 1;


drop procedure if exists get_user_datetime();
create function get_user_datetime() returning char(12),datetime year to second;

   return user, current year to second;
end function 
;

drop trigger if exists ti_my_table;
create trigger ti_my_table insert on my_table referencing new as n for each row (
  execute function get_user_datetime() into uslu, dtlu
)
;
drop trigger if exists tu_my_table;
create trigger tu_my_table update on my_table referencing new as n for each row (
  execute function get_user_datetime() into uslu, dtlu
)
;




insert into my_table  values (my_table_seq.nextval, "a", "b", null, "witz", mdy(1,1,1900)) ;

SELECT *
FROM my_table
WHERE 1=1
;

Answer 2

The answer of @user3243781 get close, but did not work because it returns the error:

-747 Table or column matches object referenced in triggering statement.

This error is returned when a triggered SQL statement acts on the triggering table, or when both statements are updates, and the column that is updated in the triggered action is the same as the column that the triggering statement updates.

So the alternative is handle with the NEW variable directly. For that you need to use a procedure with the triggers reference resource, which means the procedure will able to act like the trigger by self.

Below is my example which I run with dbaccess over a Informix v11.70.
This resource is available only for versions +11 of the engine, as far I remember.

create table teste ( Name NVARCHAR(100), Type INT , Plan NVARCHAR(100) );
Table created.

create procedure check_name_values()
  referencing new as n for teste ;;

  define check_type integer ;;
  define check_plan NVARCHAR ;;

  if upper(n.name) like 'F%' then
    let n.type = 1;;
    let n.plan = "Test";;

  end if
end procedure ;
Routine created.

;

create trigger trg_tablename_ins
    insert on teste
    referencing new as new
    for each row
    (
            execute procedure check_name_values() with trigger references

    );
Trigger created.

insert into teste values ('cesar',99,'myplan');
1 row(s) inserted.
insert into teste (name) values ('fernando');
1 row(s) inserted.
insert into teste values ('Fernando',100,'your plan');
1 row(s) inserted.



select * from teste ;
name  cesar
type  99
plan  myplan

name  fernando
type  1
plan  Test

name  Fernando
type  1
plan  Test

3 row(s) retrieved.



drop table if exists teste;
Table dropped.


drop procedure if exists check_name_values;
Routine dropped.

Answer 3

create trigger trg_tablename_ins
    insert on tablename
    referencing new as new
    for each row
    (
            execute procedure check_name_values
            (
                    new.name,
                    new.type, 
                    new.plan
            )

    );



create procedure check_name_values 
(
   name NVARCHAR,
   new_type integer,
   new_plan NVARCHAR,
)
define check_type integer ;
define check_plan NVARCHAR ;

  let check_type = 1;
  let check_plan = "Test";

   if name = 'F%'
   then
       insert into tablename (name,type,plan) values (name,check_type,check_plan);
   else
       insert into tablename (name,type,plan) values (name,new_type,new_plan);
   end if ;

end procedure ;