trigger code format changes
Posted in 2004
Why does Informix reformat the trigger code?
A sample:-
CREATE TRIGGER tflight_req
INSERT ON flight_req
REFERENCING NEW AS freq
FOR EACH ROW
(
INSERT INTO history
(company,corp_id,user_id,s_date,s_time,newid,city1,city2,
city_jaw,date_depart,date_back,airline,airclass,
login_name,corp_name,trip_type)
SELECT id[1,2],id[3,6],id[8,11],TODAY,TO_CHAR(CURRENT,'%H%M%S'),
id,city1,city2,city_jaw,date_depart,
CASE WHEN (date_back = 'N/A' ) THEN
NULL
ELSE date_back END ,
airline ,
airclass,
NVL (u.uname,login_name ),
NVL (b.bname,id[3,6]),
trip_type
FROM flight_req f,outer users u, outer branch b
WHERE id[3,6] = b.bid AND id[3,11] = u.uid
and f.id = freq.id
);
This is the format reported by dbschema:-
create trigger "dba".tflight_req insert on "dba".flight_req referencing
new as freq
for each row
(
insert into "dba".history (company,corp_id,user_id,s_date,
s_time,newid,city1,city2,city_jaw,date_depart,date_back,airline,airclass,
login_name,corp_name,trip_type) select x0.id [1,2] ,x0.id [3,6] ,
x0.id [8,11] ,TODAY ,TO_CHAR (CURRENT year to fraction(3) ,'%H%M%S'
),x0.id ,x0.city1 ,x0.city2 ,x0.city_jaw ,x0.date_depart ,CASE WHEN
(x0.date_back = 'N/A' ) THEN NULL ELSE x0.date_back END ,x0.airline
,x0.airclass ,NVL (x1.uname ,x0.login_name ),NVL (x2.bname ,x0.id
[3,6] ),x0.trip_type from "dba".flight_req x0 ,outer("dba".users x1
) ,outer("dba".branch x2 ) where (((x0.id [3,6] = x2.bid ) AND (x0.id
[3,11] = x1.uid ) ) AND (x0.id = freq.id ) ) );
As can be seen, the SQL query inside the trigger block gets rewritten and from readability
point of view, gets a C rating. This does not happen in Stored procedures.
Why this is a concern? Well we check the code (entire dbschema) into CVS and I fear that everytime
I had to pull the schema out of CVS, I get this crude format. I am finicky about indentations,
and this code just pisses me off.
We use 9.21. So I am not sure how much of it is fixed in later versions.