Re: creating procedure for audit table
> Honestly, i not so good with informix procedure. Maybe you have this
> kind procedure and trigger that running well ? so I'll grateful if you
> pass it to me.
>
> again thank's and appreciated...!
We've create an audit table one of our tables like -
-------------------------------------------------------------------------------------
create table "informix".xrefr
(
xr_serial_no serial not null filtering ,
xr_stock_no integer,
xr_desc char(30),
xr_oldcr_partcode char(24),
xr_oem_code char(4),
xr_oem_partcode char(20),
xr_vend_code char(4),
xr_vendr_oem_code char(4),
xr_vendr_part_code char(24),
xr_vendr_stock_no char(20),
xr_min_qty integer,
xr_net_price decimal(12,4),
xr_list_price decimal(12,4),
xr_quality_code char(2),
xr_weight smallint,
xr_avail_days smallint,
xr_lead_time_days smallint,
xr_update_date date,
xr_effective_date date,
xr_user_id char(10),
xr_notes_avail char(1),
xr_record_type char(2),
xr_supersede char(1),
xr_drop_ship char(1),
xr_expanded_produc char(1),
xr_reference char(1),
xr_terminated_prod char(1),
xr_manual_autoload char(1),
xr_data_source char(4),
xr_status1 char(1),
xr_status2 char(1),
xr_stock_flag char(1),
xr_extra_value char(4),
primary key (xr_serial_no) filtering
);
----------------------------------------------------------------------------------- create table "informix".xrefr_audit
(
record_id serial not null ,
xr_serial_no integer,
xr_stock_no integer,
xr_desc char(30),
xr_oem_code char(4),
xr_oem_partcode char(20),
xr_vend_code char(4),
xr_vendr_part_code char(24),
xr_vendr_stock_no char(20),
xr_min_qty integer,
xr_net_price decimal(12,4),
xr_list_price decimal(12,4),
xr_quality_code char(2),
xr_weight smallint,
xr_avail_days smallint,
xr_lead_time_days smallint,
xr_update_date date,
xr_effective_date date,
xr_user_id char(10),
xr_notes_avail char(1),
xr_record_type char(2),
xr_supersede char(1),
xr_drop_ship char(1),
xr_expanded_produc char(1),
xr_reference char(1),
xr_terminated_prod char(1),
xr_manual_autoload char(1),
xr_data_source char(4),
xr_status1 char(1),
xr_status2 char(1),
xr_stock_flag char(1),
xr_extra_value char(4),
event_time datetime year to second
default current year to second,
event_type char(1),
event_user char(10)
default user,
event_sess integer not null ,
check ((((event_type = 'I' ) OR (event_type = 'D' ) ) OR (event_type
= 'O' ) )
OR (event_type = 'N' ) ),
primary key (record_id)
);
---------------------------------------------------------------------------
CREATE PROCEDURE p_xrefr_delete(
i_stock_no integer, i_desc char(30),
i_oem_code char(4), i_oem_partcode char(20),
i_vend_code char(4), i_vendr_part_code char(24),
i_vendr_stock_no char(20), i_min_qty integer,
i_net_price decimal(12,4), i_list_price decimal(12,4),
i_quality_code char(2), i_weight smallint,
i_avail_days smallint, i_lead_time_days smallint,
i_update_date date, i_effective_date date,
i_user_id char(10), i_notes_avail char(1),
i_record_type char(2), i_supersede char(1),
i_drop_ship char(1), i_expanded_produc char(1),
i_reference char(1), i_terminated_prod char(1),
i_manual_autoload char(1), i_data_source char(4),
i_status1 char(1), i_status2 char(1),
i_stock_flag char(1), i_extra_value char(4))
insert into xrefr_audit
(xr_stock_no, xr_desc, xr_oem_code,
xr_oem_partcode, xr_vend_code,
xr_vendr_part_code, xr_vendr_stock_no,
xr_min_qty, xr_net_price, xr_list_price,
xr_quality_code, xr_weight, xr_avail_days,
xr_lead_time_days, xr_update_date,
xr_effective_date, xr_user_id,
xr_notes_avail, xr_record_type,
xr_supersede, xr_drop_ship,
xr_expanded_produc, xr_reference,
xr_terminated_prod, xr_manual_autoload,
xr_data_source, xr_status1, xr_status2,
xr_stock_flag, xr_extra_value,
event_type, event_sess)
values(i_stock_no, i_desc,
i_oem_code, i_oem_partcode,
i_vend_code, i_vendr_part_code,
i_vendr_stock_no, i_min_qty,
i_net_price, i_list_price,
i_quality_code, i_weight,
i_avail_days, i_lead_time_days,
i_update_date, i_effective_date,
i_user_id, i_notes_avail,
i_record_type, i_supersede,
i_drop_ship, i_expanded_produc,
i_reference, i_terminated_prod,
i_manual_autoload, i_data_source,
i_status1, i_status2,
i_stock_flag, i_extra_value,
'D', DBINFO('sessionid'));END PROCEDURE;
--------------------------------------------------------------
CREATE PROCEDURE p_xrefr_insert(i_stock_no integer, i_desc char(30),i_oem_code
char(4),
i_oem_partcode char(20), i_vend_code char(4), i_vendr_part_code
char(24),
i_vendr_stock_no char(20), i_min_qty integer, i_net_price
decimal(12,4),
i_list_price decimal(12,4), i_quality_code char(2), i_weight
smallint,
i_avail_days smallint, i_lead_time_days smallint, i_update_date
date,
i_effective_date date, i_user_id char(10), i_notes_avail char(1),
i_record_type char(2), i_supersede char(1), i_drop_ship char(1),
i_expanded_produc char(1), i_reference char(1), i_terminated_prod
char(1),
i_manual_autoload char(1), i_data_source char(4), i_status1 char(1),
i_status2 char(1), i_stock_flag char(1), i_extra_value char(4))
insert into xrefr_audit
(xr_stock_no, xr_desc, xr_oem_code,
xr_oem_partcode, xr_vend_code,
xr_vendr_part_code, xr_vendr_stock_no,
xr_min_qty, xr_net_price, xr_list_price,
xr_quality_code, xr_weight, xr_avail_days,
xr_lead_time_days, xr_update_date,
xr_effective_date, xr_user_id,
xr_notes_avail, xr_record_type,
xr_supersede, xr_drop_ship,
xr_expanded_produc, xr_reference,
xr_terminated_prod, xr_manual_autoload,
xr_data_source, xr_status1, xr_status2,
xr_stock_flag, xr_extra_value,
event_type, event_sess)
values(i_stock_no, i_desc,
i_oem_code, i_oem_partcode,
i_vend_code, i_vendr_part_code,
i_vendr_stock_no, i_min_qty,
i_net_price, i_list_price,
i_quality_code, i_weight,
i_avail_days, i_lead_time_days,
i_update_date, i_effective_date,
i_user_id, i_notes_avail,
i_record_type, i_supersede,
i_drop_ship, i_expanded_produc,
i_reference, i_terminated_prod,
i_manual_autoload, i_data_source,
i_status1, i_status2,
i_stock_flag, i_extra_value,
'I', DBINFO('sessionid'));END PROCEDURE;
-------------------------------------------------------------
CREATE PROCEDURE p_x