informix-trigger

/***************************************************************************************/
informix 触发器
--1 准备触发器调用存储过程
--2 表的 update触发器更新自身的 字段??
--3 视图有 INSTEAD OF 触发器
/***************************************************************************************/
1 表触发器
--1）b$a200
a: 存储过程
CREATE PROCEDURE informix.tr_b$a200_pro()
REFERENCING OLD AS OLD NEW AS NEW for b$a200;
IF INSERTING THEN
   insert into trigger_table(optype,opvalues) values('insert','P'||new.province||'_Y'||new.year);
END IF;
IF UPDATING THEN
   insert into trigger_table(optype,opvalues) values('update','P'||old.province||'_Y'||old.year||'-----------'||'P'||new.province||'_Y'||new.year);
END IF;
IF DELETING THEN
   insert into trigger_table(optype,opvalues) values('delete','P'||old.province||'_Y'||old.year);
END IF;


IF SELECTING THEN
 insert into trigger_table(optype,opvalues) values('select','P'||old.province||'_Y'||old.year);
END IF;
end procedure;     


b: 为每个形式编写一个触发器  
CREATE TRIGGER informix.tr_b$a200_insert insert on "informix".b$a200    for each row
        (
        execute procedure "informix".tr_b$a200_pro() with trigger references );


CREATE TRIGGER informix.tr_b$a200_update update on "informix".b$a200    for each row
        (
        execute procedure "informix".tr_b$a200_pro() with trigger references );


CREATE TRIGGER informix.tr_b$a200_select select on "informix".b$a200    for each row
        (
        execute procedure "informix".tr_b$a200_pro() with trigger references );


c: 有外键的 表的 触发器
/*因为有外键 ON DELETE CASCADE 所以 不能加 delete TRIGGER
CREATE TRIGGER informix.tr_b$a200_delete delete on "informix".b$a200    for each row
        (
        execute procedure "informix".tr_b$a200_pro() with trigger references );
*/


--2) b$a300
a: 存储过程
CREATE PROCEDURE informix.tr_b$a300_pro()
REFERENCING OLD AS OLD NEW AS NEW for b$a300;
IF INSERTING THEN
   insert into trigger_table(optype,opvalues) values('insert','P'||new.province||'_Y'||new.year);
END IF;
IF UPDATING THEN
   insert into trigger_table(optype,opvalues) values('update','P'||old.province||'_Y'||old.year||'-----------'||'P'||new.province||'_Y'||new.year);
END IF;
IF DELETING THEN
   insert into trigger_table(optype,opvalues) values('delete','P'||old.province||'_Y'||old.year);
END IF;


IF SELECTING THEN
 insert into trigger_table(optype,opvalues) values('select','P'||old.province||'_Y'||old.year);
END IF;
end procedure;     


b: 为每个形式编写一个触发器  
CREATE TRIGGER informix.tr_b$a300_insert insert on "informix".b$a300    for each row
        (
        execute procedure "informix".tr_b$a300_pro() with trigger references );


CREATE TRIGGER informix.tr_b$a300_update update on "informix".b$a300    for each row
        (
        execute procedure "informix".tr_b$a300_pro() with trigger references );


CREATE TRIGGER informix.tr_b$a300_select select on "informix".b$a300    for each row
        (
        execute procedure "informix".tr_b$a300_pro() with trigger references );


CREATE TRIGGER informix.tr_b$a300_delete delete on "informix".b$a300    for each row
        (
        execute procedure "informix".tr_b$a300_pro() with trigger references );




2 视图触发器 


--1) a300
a: 存储过程
CREATE PROCEDURE informix.tr_a300_pro()
REFERENCING OLD AS OLD NEW AS NEW for a300;
IF INSERTING THEN
   insert into a200(a,b,c) values(new.a,new.b,new.b);
END IF;
IF UPDATING THEN
   update a200 set(a,b,c)= (new.a,new.b,new.c) where a=old.a ;
END IF;
IF DELETING THEN
   delete from a200 where a=old.a ;
END IF;
end procedure;     


b: 为每个形式编写一个触发器  
CREATE TRIGGER informix.tr_a300_insert INSTEAD OF insert on "informix".a300    for each row
        (
        execute procedure "informix".tr_a300_pro() with trigger references );


CREATE TRIGGER informix.tr_a300_update INSTEAD OF update on "informix".a300    for each row
        (
        execute procedure "informix".tr_a300_pro() with trigger references );


CREATE TRIGGER informix.tr_a300_delete INSTEAD OF delete on "informix".a300    for each row
        (
        execute procedure "informix".tr_a300_pro() with trigger references );
 
--2）a900
CREATE PROCEDURE informix.tr_a900_pro()
REFERENCING OLD AS OLD NEW AS NEW for a900;
IF INSERTING THEN
   insert into b$a900(a,province,year) values(new.a,new.province,new.year);
END IF;
IF UPDATING THEN
   update b$a900 set(a,province,year)= (new.a,new.province,new.year) where a=old.a ;
END IF;
IF DELETING THEN
   delete from b$a900 where a=old.a ;
END IF;
end procedure;     


CREATE TRIGGER informix.tr_a900_insert INSTEAD OF insert on "informix".a900    for each row
        (
        execute procedure "informix".tr_a900_pro() with trigger references );


CREATE TRIGGER informix.tr_a900_update INSTEAD OF update on "informix".a900    for each row
        (
        execute procedure "informix".tr_a900_pro() with trigger references );


CREATE TRIGGER informix.tr_a900_delete INSTEAD OF delete on "informix".a900    for each row
        (
        execute procedure "informix".tr_a900_pro() with trigger references );
 
3 update of 触发器
create trigger tr_sales update of custer_id,order_id,prod_name on sales
REFERENCING NEW AS NEW FOR EACH ROW (update sales set update_time = current where id=new.id);


 
4 时间戳的实现
1） 时间类型字段的定义
CREATE TABLE ExampleDatesAndTimes
(
    rownumber     SERIAL NOT NULL PRIMARY KEY,
    date_column   DATE DEFAULT TODAY NOT NULL,
    datetime_yd   DATETIME YEAR TO DAY
                  DEFAULT CURRENT YEAR TO DAY NOT NULL,
    datetime_ys   DATETIME YEAR TO SECOND
                  DEFAULT CURRENT YEAR TO SECOND NOT NULL,
    datetime_hs   DATETIME HOUR TO SECOND
                  DEFAULT CURRENT HOUR TO SECOND NOT NULL,
    datetime_yf5   DATETIME YEAR TO FRACTION(5)
                  DEFAULT CURRENT YEAR TO FRACTION(5) NOT NULL,
    payload       VARCHAR(255) NOT NULL
);


INSERT INTO ExampleDatesAndTimes(Payload) VALUES ("Hello");


INSERT INTO ExampleDatesAndTimes
    VALUES(0, '1066-10-14', '2005-01-01', '2017-11-10 09:08:07',
           '22:23:21', '2012-11-10 09:08:07.00300','Gezundheit');


> select * from  ExampleDatesAndTimes;


rownumber     1
date_column   2016-11-19
datetime_yd   2016-11-19
datetime_ys   2016-11-19 15:50:47
datetime_hs   15:50:47
datetime_yf5  2016-11-19 15:50:47.00000
payload       Hello


rownumber     2
date_column   1066-10-14
datetime_yd   2001-01-01
datetime_ys   2012-11-10 09:08:07
datetime_hs   23:23:21
datetime_yf5  2012-11-10 09:08:07.00000
payload       Gezundheit




2） 时间戳的实现（insert 操作采用DEFAULT 值实现，UPDATE采用 触发器update of 字段实现）


drop table sales;
create table sales( 
id serial, 
custer_id int,
order_id int,
prod_name varchar(30),
insert_time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND ,
update_time DATETIME YEAR TO SECOND);


insert into sales(custer_id,order_id,prod_name) values(1,1,'桌子');
insert into sales(custer_id,order_id,prod_name) values(2,2,'电脑');
insert into sales(custer_id,order_id,prod_name) values(3,3,'投影仪');
insert into sales(custer_id,order_id,prod_name) values(4,4,'电视');


> select * from sales;
id           1
custer_id    1
order_id     1
prod_name    桌子
insert_time  2016-11-19 17:33:16
update_time  


id           2
custer_id    2
order_id     2
prod_name    电脑
insert_time  2016-11-19 17:33:16
update_time  


id           3
custer_id    3
order_id     3
prod_name    投影仪
insert_time  2016-11-19 17:33:16
update_time  


id           4
custer_id    4
order_id     4
prod_name    电视
insert_time  2016-11-19 17:33:16
update_time  


4 row(s) retrieved.


drop trigger tr_sales;


--如果是下面的触发器 形式，则会报747 错误
> create trigger tr_sales update  on sales
for each row (update sales set update_time = current);


> update sales set prod_name='电视机' where id=4;


  747: Table or column matches object referenced in triggering statement.
Error in line 1
Near character position 48


drop trigger tr_sales;


--使用存储过程 没有问题 
drop PROCEDURE informix.tr_sales_pro();
CREATE PROCEDURE informix.tr_sales_pro()
REFERENCING OLD AS OLD NEW AS NEW for sales;
if(updating) then
let NEW.update_time=current;
 end if;
 end procedure; 


drop TRIGGER tr_sales;


CREATE TRIGGER informix.tr_sales update  on sales    for each row
        (
        execute procedure "informix".tr_sales_pro() with trigger references );




update sales set prod_name='电视机' where id=4;


 
drop TRIGGER tr_sales;


--需要创建 update of 字段类型的 触发器
create trigger tr_sales update of custer_id,order_id,prod_name on sales
REFERENCING NEW AS NEW FOR EACH ROW (update sales set update_time = current where id=new.id);


 
> update sales set custer_id=100,order_id=1000 ;
> select * from sales;
id           1
custer_id    100
order_id     1000
prod_name    桌子
insert_time  2016-11-19 17:37:27
update_time  2016-11-19 18:06:04


id           2
custer_id    100
order_id     1000
prod_name    电脑
insert_time  2016-11-19 17:37:27
update_time  2016-11-19 18:06:04


id           3
custer_id    100
order_id     1000
prod_name    投影仪
insert_time  2016-11-19 17:37:27
update_time  2016-11-19 18:06:04


id           4
custer_id    100
order_id     1000
prod_name    电视机
insert_time  2016-11-19 17:37:27
update_time  2016-11-19 18:06:04


附录:为表增加 时间戳字段
1 动态创建SQL
drop procedure if exists EXECDLL_LONG;


CREATE PROCEDURE EXECDLL_LONG (strsql lvarchar(32739))
define v_message lvarchar(32739);
ON EXCEPTION
let v_message='script error';
END EXCEPTION;
EXECUTE IMMEDIATE strsql;
--EXECUTE PROCEDURE proc_displayfromfile(strsql);
END PROCEDURE ;


2 动态创建表
drop procedure if exists sp_Create_Table; 
create procedure sp_Create_Table(Table_Con lvarchar(32739))
        define v_sql lvarchar(32739);
        LET v_sql=TRIM(Table_Con);
        execute procedure ExecDll_Long(v_sql);
end procedure;




3 动态创建表


drop procedure sp_Create_Table_by_name_col;
create procedure sp_Create_Table_by_name_col(tabname_pre varchar(30),tabnum int,colnum int)
define v_sql lvarchar(32739);
define v_coldef lvarchar(32739);
define v_colname varchar(30);
define v_tabname varchar(30);
define k int;
define i int;
for k= 1 to tabnum
--表名
let v_tabname=tabname_pre||k;


--列定义
let v_coldef='';
FOR i = 1 TO colnum
let v_colname='col'||i;
let v_coldef=v_coldef||v_colname||' int ,';
END FOR;
let v_coldef=substr(v_coldef,1,length(v_coldef)-1);


--建表SQL
let v_sql='create table '||v_tabname|| '('||v_coldef||');';


--创建表
execute procedure sp_Create_Table(v_sql);
end for;
end procedure;


--动态创建100个表
execute procedure sp_Create_Table_by_name_col('test_',10,100);


4 动态给表加字段


drop procedure sp_alter_Table_col;
create procedure sp_alter_Table_col(tabname varchar(30),coldef varchar(200))
        define v_sql lvarchar(32739);
let v_sql='alter  table '||tabname|| ' add '||coldef;
        execute procedure sp_Create_Table(v_sql);
end procedure;


5 给test 开头的 表增加 insert_time,update_time　2个 字段


drop procedure sp_add_Table_col;
create procedure sp_add_Table_col(tabname_pre varchar(30))
define v_coldef1 varchar(200);
define v_coldef2 varchar(200);
define v_tabname varchar(30);
let v_coldef1='insert_time DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SECOND';
let v_coldef2='update_time datetime year to second';


foreach select tabname into v_tabname from systables where tabname like tabname_pre||'%'
execute procedure sp_alter_Table_col(v_tabname,v_coldef1);
execute procedure sp_alter_Table_col(v_tabname,v_coldef2);
end foreach;
end procedure;


--增加 字段
execute procedure sp_add_Table_col('test_');


6 给每个表增加触发器
 
drop procedure sp_add_Table_trigger;
create procedure sp_add_Table_trigger(tabname_pre varchar(30))
define v_sql lvarchar(32739);
define v_colstr lvarchar(32739);


define v_tabname varchar(30);
SET DEBUG FILE TO '/home/informix/info_debug.txt';
TRACE 'BEGIN TRACE' ;
trace on;


foreach select tabname into v_tabname from systables where tabname like tabname_pre||'%'
--非时间戳字段的组合
select rtrim(strsum(colname),',') into v_colstr from systables s1,syscolumns s2 where s1.tabid=s2.tabid and s1.tabname=v_tabname and colname not in('insert_time','update_time');


--trigger sql 
let v_sql='create trigger tr_'||v_tabname||' update of '||v_colstr||' on '||v_tabname||' REFERENCING NEW AS NEW FOR EACH ROW (update '||v_tabname||' set update_time = current where col1=new.col1)';
--创建trigger 
execute procedure ExecDll_Long(v_sql);
end foreach;
end procedure;


--测试
execute procedure sp_add_Table_trigger('test_');
 
select  trigname from SYSTRIGGERS s1,systables s2 where s1.tabid=s2.tabid and s2.tabname like 'test_%';




insert into test_1(col1) values(1);
insert into test_1(col1) values(1);
insert into test_1(col1) values(1);


insert into test_10(col1) values(10);
insert into test_10(col1) values(10);
insert into test_10(col1) values(10);
 
select col1,insert_time,update_time from test_1;
select col1,insert_time,update_time from test_10;


update test_1 set col2=11;
update test_10 set col2=111;


select col1,col2,insert_time,update_time from test_1;
select col1,col2,insert_time,update_time from test_10;