Informix trigger to change inserted values

问题:

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.

回答1:

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.

回答2:

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 ;