delimiter //

create or replace view ta_triggers0 as
with e as
(
select t.tab_name,s.operation_type,concat(lower(tab_name),'_a',lower(operation_type),'rtrg') trigger_name,t.tab_id from
tab t
cross join (select 'I' operation_type union all select 'U' union all select 'D') s
where not t.tab_type_id=0
)
,k as
(
select tab_name,operation_type, trigger_name,col_name,col_typ, tab_id, col_id from tab_col a join e using (tab_id) join col b using (col_id)
)
SELECT
tab_name,operation_type,trigger_name,
cast(
concat(
rtrim("delimiter //                    "),char(10),
"CREATE OR REPLACE TRIGGER ",trigger_name,char(10),
rtrim(" AFTER"),char(10),
" ",case operation_type when 'I' then 'INSERT' when 'U' then 'UPDATE' when 'D' then 'DELETE' end,char(10),
" ON ",tab_name,char(10),
" FOR EACH ROW",char(10),
rtrim("begin                                                              "),char(10),
rtrim("DECLARE nau MEDIUMINT UNSIGNED;                                    "),char(10),
rtrim("declare bar DATETIME;                                              "),char(10),
rtrim("declare usr MEDIUMINT UNSIGNED;                                    "),char(10),
rtrim("declare ws BIGINT UNSIGNED;                                        "),char(10),
rtrim("declare cut MEDIUMINT UNSIGNED;                                    "),char(10),
rtrim("SELECT NEXTVAL('AU_SEQ'), user_id, ses_id, now() INTO nau, usr, ws, bar FROM st_curr_user;                                                "),char(10),
"SELECT tab_id INTO cut FROM tab WHERE tab_name = '",tab_name,"';",char(10),
"INSERT INTO au (db_time, au_nr, user_id, tab_id, operation_type, ses_id) VALUES (bar,nau,usr,cut,'",operation_type,"',ws);",char(10),
"INSERT INTO gttn (d1,id1,id2,c1,c2,c0) VALUES (bar,nau,cut,'",tab_name,"','",operation_type,"','totoro');",char(10),
 "") as char) ddl
,0 ind
from e
UNION all
SELECT tab_name,operation_type,trigger_name,
CASE col_typ
WHEN 'I' THEN
Concat("INSERT INTO au_idata (db_time,au_nr,col_id"
,IF(operation_type IN ('D','U'),",old_val","")
,IF(operation_type IN ('I','U'),",new_val","")
,") SELECT bar,nau,col_id"
,IF(operation_type IN ('D','U'),concat(",old.",col_name),"")
,IF(operation_type IN ('I','U'),concat(",new.",col_name),"")
,Concat(" FROM col WHERE col_name = '",col_name,"';")
)
WHEN 'N' THEN
Concat("INSERT INTO au_ndata (db_time,au_nr,col_id"
,IF(operation_type IN ('D','U'),",old_val","")
,IF(operation_type IN ('I','U'),",new_val","")
,") SELECT bar,nau,col_id"
,IF(operation_type IN ('D','U'),concat(",old.",col_name),"")
,IF(operation_type IN ('I','U'),concat(",new.",col_name),"")
,Concat(" FROM col WHERE col_name = '",col_name,"';")
)
WHEN 'D' THEN
Concat("INSERT INTO au_ddata (db_time,au_nr,col_id"
,IF(operation_type IN ('D','U'),",old_val","")
,IF(operation_type IN ('I','U'),",new_val","")
,") SELECT bar,nau,col_id"
,IF(operation_type IN ('D','U'),concat(",old.",col_name),"")
,IF(operation_type IN ('I','U'),concat(",new.",col_name),"")
,Concat(" FROM col WHERE col_name = '",col_name,"';")
)
WHEN 'C' THEN
Concat(
IF(operation_type IN ('I','U'),
Concat(
"set @@optimizer_switch='derived_merge=OFF';",char(10)
,"INSERT INTO ae (ae_hash, text)",char(10)
,"WITH c AS",char(10)
,"(",char(10)
,"SELECT",char(10)
,"conv(substring(md5(new.",col_name,"),1,4),16,10) mod 9999 ae_hash",char(10)
,",new.",col_name," text",char(10)
,"FROM dual",char(10)
,"WHERE",char(10)
,"NOT new.",col_name," IS NULL",char(10)
,IF(operation_type='U',concat("and NOT new.",col_name,"<=>old.",col_name,char(10)),"")
,")",char(10)
,"SELECT ae_hash,text",char(10)
,"FROM c WHERE NOT EXISTS (SELECT 0 FROM ae WHERE ae_hash=c.ae_hash AND text=c.text);",char(10)
,"set session optimizer_switch=default;",char(10)),"")
,"INSERT INTO au_cdata (db_time,au_nr,col_id",char(10)
,IF(operation_type IN ('D','U'),",old_ae_id","")
,IF(operation_type IN ('I','U'),",new_ae_id","")
,")",char(10)
,"SELECT bar,nau,col_id",char(10)
,IF(operation_type IN ('D','U'),",b.ae_id","")
,IF(operation_type IN ('I','U'),",c.ae_id",""),char(10)
,"FROM col a",char(10)
,IF(operation_type IN ('D','U'),Concat("left join ae b ON (conv(substring(md5(old.",col_name,"),1,4),16,10) mod 9999=b.ae_hash AND old.",col_name,"=b.text)",CHAR(10)),"")
,IF(operation_type IN ('I','U'),concat("left join ae c ON (conv(substring(md5(new.",col_name,"),1,4),16,10) mod 9999=c.ae_hash AND new.",col_name,"=c.text)",CHAR(10)),"")
,"WHERE col_name='",col_name,"';"
)
end
ddl,Rank() over (PARTITION BY tab_id ORDER BY col_id) ind FROM k
UNION ALL
SELECT tab_name,operation_type,trigger_name
,
CAST(
Concat(
rtrim("-- STATEMENT part                                                                                                                      "),char(10),
IF(operation_type in ('I','U'),
concat(
rtrim("-- papildyti/pakeisti/ismesti cold_data                                                                                                "),char(10),
rtrim("-- part 1 not SYTA                                                                                                                     "),char(10),
rtrim("drop temporary table if exists u;                                                                                                      "),char(10),
rtrim("create temporary table u as                                                                                                            "),char(10),
rtrim("-- col_id, col_val, nr, desc_col_id, desc_ae_id                                                                                        "),char(10),
rtrim("SELECT i.col_id, i.new_val col_val                                                                                                     "),char(10),
rtrim(",d.nr,d.desc_col_id,c.new_ae_id desc_ae_id                                                                                             "),char(10),
"FROM (SELECT d1 db_time, id1 au_nr, id2 tab_id, c2 operation_type FROM gttn WHERE c1='",tab_name,"' AND c0='totoro' AND c2 IN ('I','U')) a",char(10),
rtrim("join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr)                                                                           "),char(10),
rtrim("join tab_col b ON (i.col_id=b.col_id AND b.col_bp=1)                                                                                   "),char(10),
rtrim("join cold d ON (i.col_id=d.col_id)                                                                                                     "),char(10),
rtrim("join au_cdata c ON (a.db_time=c.db_time AND a.au_nr=c.au_nr AND d.desc_col_id=c.col_id)                                                "),char(10),
rtrim("WHERE (a.operation_type='I' OR not c.old_ae_id<=>c.new_ae_id);                                                                         "),char(10),
rtrim("DELETE from cold_data WHERE (col_id,col_val,nr) IN (SELECT col_id, col_val, nr FROM u WHERE desc_ae_id IS null);                       "),char(10),
rtrim("                                                                                                                                       "),char(10),
rtrim("UPDATE cold_data y, u SET                                                                                                              "),char(10),
rtrim(" y.desc_col_id = u.desc_col_id                                                                                                         "),char(10),
rtrim(",y.desc_ae_id  = u.desc_ae_id                                                                                                          "),char(10),
rtrim("WHERE u.col_id=y.col_id and u.col_val=y.col_val and u.nr=y.nr                                                                          "),char(10),
rtrim("AND NOT                                                                                                                                "),char(10),
rtrim("(y.desc_col_id = u.desc_col_id                                                                                                         "),char(10),
rtrim("AND y.desc_ae_id  = u.desc_ae_id);                                                                                                     "),char(10),
rtrim("                                                                                                                                       "),char(10),
rtrim("insert into cold_data (col_id, col_val, nr, desc_col_id, desc_ae_id)                                                                   "),char(10),
rtrim("select col_id, col_val, nr, desc_col_id, desc_ae_id from u                                                                             "),char(10),
rtrim("where not exists (select 0 from cold_data where col_id=y.col_id and col_val=y.col_val and nr=y.nr);                                    "),char(10),
rtrim("                                                                                                                                       "),char(10),
rtrim("drop temporary table if exists u;                                                                                                      ")
)
,"")
)
 AS CHAR)
ddl,3000 ind FROM e
UNION ALL
SELECT tab_name,operation_type,trigger_name
,
CAST(
IF(operation_type IN ('I','U'),
Concat(
rtrim("-- papildyti/pakeisti/ismesti colc_data                                                                                                   "),char(10),
rtrim("drop temporary table if exists u;                                                                                                         "),char(10),
rtrim("create temporary table u as                                                                                                               "),char(10),
rtrim("WITH l AS                                                                                                                                 "),char(10),
rtrim("(                                                                                                                                         "),char(10),
rtrim("SELECT                                                                                                                                    "),char(10),
rtrim("a.*                                                                                                                                       "),char(10),
rtrim(",i.col_id                                                                                                                                 "),char(10),
rtrim(",i.new_val                                                                                                                                "),char(10),
rtrim(",Max(if(c.col_id=i.col_id,i.col_id,null)) over (PARTITION BY a.db_time, a.au_nr) bcol_id                                                  "),char(10),
rtrim(",Max(if(c.col_id=i.col_id,i.new_val,null)) over (PARTITION BY a.db_time, a.au_nr) bcol_val                                                "),char(10),
rtrim(" FROM                                                                                                                                     "),char(10),
"(SELECT d1 db_time, id1 au_nr, id2 tab_id, c2 operation_type FROM gttn WHERE c1='",tab_name,"' AND c0='totoro' AND c2 IN ('I','U')) a",char(10),
rtrim("join au_idata i ON (a.db_time=i.db_time AND a.au_nr=i.au_nr)                                                                              "),char(10),
rtrim("left join tab_col c ON (a.tab_id=c.tab_id AND i.col_id=c.col_id AND c.col_bp=1)                                                           "),char(10),
rtrim("WHERE (a.operation_type='I' OR not i.old_val<=>i.new_val)                                                                                 "),char(10),
rtrim(")                                                                                                                                         "),char(10),
rtrim("SELECT b.col_id, l.bcol_val col_val                                                                                                       "),char(10),
rtrim(",b.nr, b.cont_col_id, l.new_val cont_col_val                                                                                              "),char(10),
rtrim(" FROM                                                                                                                                     "),char(10),
rtrim("l join colc b ON (l.bcol_id=b.col_id AND l.col_id=b.cont_col_id)                                                                          "),char(10),
rtrim("WHERE NOT bcol_val IS NULL;                                                                                                               "),char(10),
rtrim("DELETE from colc_data WHERE (col_id,col_val,nr) IN                                                                                        "),char(10),
rtrim("(SELECT col_id,col_val,nr FROM u WHERE cont_col_val IS null);                                                                             "),char(10),
rtrim("                                                                                                                                          "),char(10),
rtrim("UPDATE colc_data y, u SET                                                                                                                 "),char(10),
rtrim(" y.cont_col_id = u.cont_col_id                                                                                                            "),char(10),
rtrim(",y.cont_col_val  = u.cont_col_val                                                                                                         "),char(10),
rtrim("WHERE u.col_id=y.col_id and u.col_val=y.col_val and u.nr=y.nr                                                                             "),char(10),
rtrim("and not u.cont_col_val is null                                                                                                            "),char(10),
rtrim("AND NOT                                                                                                                                   "),char(10),
rtrim("(y.cont_col_id = u.cont_col_id                                                                                                            "),char(10),
rtrim("AND y.cont_col_val  = u.cont_col_val);                                                                                                    "),char(10),
rtrim("                                                                                                                                          "),char(10),
rtrim("insert into colc_data (col_id, col_val, nr, cont_col_id, cont_col_val)                                                                    "),char(10),
rtrim("select col_id, col_val, nr, cont_col_id, cont_col_val from u                                                                              "),char(10),
rtrim("where not u.cont_col_val is null and not exists (select 0 from colc_data where col_id=y.col_id and col_val=y.col_val and nr=y.nr);        "),char(10),
rtrim("                                                                                                                                          "),char(10),
rtrim("drop temporary table if exists u;                                                                                                         "),char(10),
"DELETE from gttn WHERE c1='",tab_name,"' AND c0='totoro';",char(10),
rtrim("                                                                                                                                          "),char(10),
rtrim("                                                                                                                                          "),char(10),
rtrim("END                                                                                                                                       "),char(10),
rtrim("//                                                                                                                                        "),char(10),
rtrim("delimiter ;                                                                                                                               "),char(10)
),"")
AS CHAR)
ddl,4000 ind FROM e

//

delimiter ;