Informix触发器以更改插入的值 [英] Informix trigger to change inserted values

问题描述

我想在插入列值之前更改几个. 我正在使用Informix作为数据库.

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

我有一个包含3列的表格:名称(NVARCHAR)，类型(INT)，计划(NVARCHAR).

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

每次插入新记录时，我都想在插入它之前检查Name值.如果名称"以F开头，我想将类型"值设置为1，将计划名称"设置为测试"

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. 对于每个新插入，首先检查Name值是否以F开头.
2. 如果是，请将类型和计划"设置为1，然后测试"，然后插入.
3. 如果否，则按原样插入值.

我已经使用BEFORE和AFTER查找了CREATE TRIGGER语句.但是，我想举一个更清晰的例子.我的案件可能会涉及之前.

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.

推荐答案

@ user3243781的答案接近，但由于返回错误而无法使用:

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

-747表或列与触发语句中引用的对象匹配.

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

当触发的SQL语句对 触发表，或者两个语句都更新时，该列 在触发操作中更新的列与 触发语句更新.

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.

因此，替代方法是直接使用NEW变量处理. 为此，您需要使用带有 触发器参考 资源的过程，这意味着该过程将能够像触发器一样自行运行.

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.

下面是我在Informix v11.70上与dbaccess一起运行的示例.
据我所知，此资源仅适用于+11版的引擎.

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.

这篇关于Informix触发器以更改插入的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！