428 lines
27 KiB
C
428 lines
27 KiB
C
#ifndef _COMSQL_H
|
|
#define _COMSQL_H
|
|
/*
|
|
* COMSQL.H
|
|
* $Revision$
|
|
* $Id$
|
|
*/
|
|
|
|
/*
|
|
* Opmerkingen:
|
|
* - gebruik ~ waar een \n gewenst is (zie bv. CREATE_TABLE())
|
|
*/
|
|
|
|
#define STRINGIZE(a) #@a
|
|
#define _EVALCONCAT(a, b) a ## b
|
|
#define EVALCONCAT(a, b) _EVALCONCAT(a, b)
|
|
#define _MKSTRING(a) STRINGIZE(a)
|
|
|
|
/*
|
|
* The database schema version must increase with every release
|
|
* It is now defined by external macro _DBV
|
|
*/
|
|
/* The minimum required software version */
|
|
#define _DBV_STRING _MKSTRING(_DBV)
|
|
|
|
#define REGISTERONCE(svnid) \
|
|
BEGIN adm.systrackscriptId(svnid, 1); END;~/
|
|
|
|
#define REGISTERRUN(svnid) \
|
|
BEGIN adm.systrackscriptId(svnid, 0); END;~/
|
|
|
|
#define SET_VERWIJDER_CHILDREN(child_table, parent_key, parent_verwijder, \
|
|
child_verwijder, table) \
|
|
BEGIN \
|
|
IF (:new.parent_verwijder IS NOT NULL AND :old.parent_verwijder IS NULL) \
|
|
THEN \
|
|
UPDATE child_table \
|
|
SET child_verwijder = :new.parent_verwijder \
|
|
WHERE \
|
|
child_table.parent_key = :new.parent_key \
|
|
AND child_verwijder IS NULL; \
|
|
END IF; \
|
|
END
|
|
|
|
#define CHECK_REFERENCE_UITVOERENDE(key_veld, message) \
|
|
DECLARE \
|
|
dummy CHAR; \
|
|
BEGIN \
|
|
IF :new.key_veld IS NOT NULL \
|
|
THEN \
|
|
SELECT 'X' INTO dummy \
|
|
FROM mld_v_uitvoerende \
|
|
WHERE MLD_UITVOERENDE_KEY = :new.key_veld; \
|
|
END IF; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
raise_application_error(-20000, message); \
|
|
END
|
|
|
|
#define CHECK_KEY_REFERENCE(table, primary_key, ref_key, message) \
|
|
DECLARE \
|
|
dummy NUMBER(10); \
|
|
mutating_table EXCEPTION; \
|
|
PRAGMA EXCEPTION_INIT(mutating_table, -4091); \
|
|
BEGIN \
|
|
SELECT primary_key \
|
|
INTO dummy \
|
|
FROM table \
|
|
WHERE primary_key = ref_key; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND \
|
|
THEN raise_application_error(-20000, message); \
|
|
WHEN mutating_table \
|
|
THEN NULL; \
|
|
END
|
|
|
|
|
|
#define CREATE_TYPE(type_name) \
|
|
CREATE TYPE type_name
|
|
|
|
#define CREATE_TABLE(table_name, level) \
|
|
CREATE TABLE table_name
|
|
|
|
#define CREATE_SEQUENCE(sequence_name, minval) \
|
|
CREATE SEQUENCE sequence_name MINVALUE minval;
|
|
|
|
#define CREATE_VIEW(view_name, level) \
|
|
CREATE OR REPLACE FORCE VIEW view_name
|
|
|
|
#define CREATE_TRIGGER(trigger_name) \
|
|
CREATE OR REPLACE TRIGGER trigger_name
|
|
|
|
#define CREATE_PROCEDURE(pack_name, proc_name) \
|
|
CREATE OR REPLACE PROCEDURE proc_name
|
|
|
|
#define VARCHAR \
|
|
VARCHAR2
|
|
|
|
#define INSTR2(a, b) \
|
|
INSTR(a, b)
|
|
|
|
#define UPDATE_UPPER(omschrijving, upper_omschrijving,tab) \
|
|
:new.upper_omschrijving := UPPER(:new.omschrijving)
|
|
|
|
#define UPDATE_AANMAAKDATUM(TableName, Datum_veld) \
|
|
BEGIN \
|
|
IF :new.Datum_veld IS NULL \
|
|
THEN \
|
|
:new.Datum_veld := SYSDATE; \
|
|
END IF; \
|
|
END
|
|
|
|
#define DATE_TO_CHAR \
|
|
TO_CHAR
|
|
|
|
#define NUMBER_TO_CHAR \
|
|
TO_CHAR
|
|
|
|
#define _TO_CHAR(x) \
|
|
TO_CHAR(x)
|
|
|
|
#define _TO_DATE2(x,y) \
|
|
TO_DATE(x,y)
|
|
|
|
#define _END_IF \
|
|
END IF;
|
|
|
|
#define _FAC_MODULE(module,omschrijving) \
|
|
INSERT INTO fac_module (fac_module_name, fac_module_version, fac_module_date, fac_module_remark, fac_module_extern) \
|
|
VALUES (module,_DBV_STRING,TO_CHAR(SYSDATE,'YYYYMMDD'),omschrijving, 1);
|
|
|
|
#define APPLICATION_ERROR_GOTO(code,text) raise_application_error(code, text)
|
|
|
|
#define UPDATE_PRIMARY_KEY(primary_key, sequence_primary_key) \
|
|
IF :new.primary_key IS NULL \
|
|
THEN SELECT sequence_primary_key.nextval INTO :new.primary_key \
|
|
FROM DUAL; \
|
|
END IF
|
|
|
|
#define LAAT_VERDIEPING_VERVALLEN_VOOR_GEBOUW(exc_msg_VERDIEPING_IN_GEBRUIK) \
|
|
BEGIN \
|
|
IF :new.alg_gebouw_verwijder IS NOT NULL \
|
|
AND :old.alg_gebouw_verwijder IS NULL \
|
|
THEN \
|
|
UPDATE alg_verdieping \
|
|
SET alg_verdieping_verwijder = :new.alg_gebouw_verwijder \
|
|
WHERE \
|
|
alg_verdieping.alg_gebouw_key = :new.alg_gebouw_key \
|
|
AND alg_verdieping_verwijder IS NULL; \
|
|
END IF; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND \
|
|
THEN raise_application_error(-20000, exc_msg_VERDIEPING_IN_GEBRUIK); \
|
|
\
|
|
WHEN TOO_MANY_ROWS \
|
|
THEN raise_application_error(-20000, exc_msg_VERDIEPING_IN_GEBRUIK); \
|
|
END
|
|
|
|
#define LAAT_RUIMTE_VERVALLEN_VOOR_VERDIEPING(exc_msg_RUIMTE_IN_GEBRUIK) \
|
|
BEGIN \
|
|
IF :new.alg_verdieping_verwijder IS NOT NULL \
|
|
AND :old.alg_verdieping_verwijder IS NULL \
|
|
THEN \
|
|
UPDATE alg_ruimte \
|
|
SET alg_ruimte_verwijder = :new.alg_verdieping_verwijder \
|
|
WHERE \
|
|
alg_ruimte.alg_verdieping_key = :new.alg_verdieping_key \
|
|
AND alg_ruimte_verwijder IS NULL; \
|
|
END IF; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND \
|
|
THEN raise_application_error(-20000, exc_msg_RUIMTE_IN_GEBRUIK); \
|
|
\
|
|
WHEN TOO_MANY_ROWS \
|
|
THEN raise_application_error(-20000, exc_msg_RUIMTE_IN_GEBRUIK); \
|
|
END
|
|
|
|
#define CHECK_NOG_REFERENCES(tabel,tabel_verwijder_veld,reference_view, \
|
|
key_veld,message) \
|
|
DECLARE \
|
|
dummy CHAR; \
|
|
BEGIN \
|
|
IF :new.tabel_verwijder_veld IS NOT NULLDATUM \
|
|
AND :old.tabel_verwijder_veld IS NULLDATUM \
|
|
THEN \
|
|
SELECT 'x' INTO dummy \
|
|
FROM reference_view \
|
|
WHERE reference_view.key_veld = :old.key_veld; \
|
|
raise_application_error(-20000, message); \
|
|
END IF; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
NULL; \
|
|
WHEN TOO_MANY_ROWS THEN \
|
|
raise_application_error(-20000, message); \
|
|
END
|
|
|
|
#define CHECK_REFERENCE_ONROERENDGOED(key_veld, message) \
|
|
DECLARE \
|
|
dummy CHAR; \
|
|
BEGIN \
|
|
SELECT 'X' INTO dummy \
|
|
FROM alg_v_aanwezigruimte \
|
|
WHERE alg_ruimte_key = :new.key_veld; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
BEGIN \
|
|
SELECT 'X' INTO dummy \
|
|
FROM alg_v_aanwezigverdieping \
|
|
WHERE alg_verdieping_key = :new.key_veld; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
BEGIN \
|
|
SELECT 'X' INTO dummy \
|
|
FROM alg_v_aanweziggebouw \
|
|
WHERE alg_gebouw_key = :new.key_veld; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
BEGIN \
|
|
SELECT 'X' INTO dummy \
|
|
FROM alg_v_aanwezigterreinsector \
|
|
WHERE alg_terreinsector_key = :new.key_veld; \
|
|
EXCEPTION \
|
|
WHEN NO_DATA_FOUND THEN \
|
|
raise_application_error(-20000, message); \
|
|
END; \
|
|
END; \
|
|
END; \
|
|
END
|
|
|
|
#define CEIL \
|
|
CEIL
|
|
|
|
#define NULLDATUM NULL
|
|
|
|
#define DEFINIEER_VIEW_AANWEZIG(table, verwijder_veld, view_name, level) \
|
|
CREATE_VIEW (view_name,level) AS \
|
|
SELECT * \
|
|
FROM table \
|
|
WHERE table.verwijder_veld IS NULLDATUM
|
|
|
|
#define DELETE_CHILDREN(child_table, parent_key) \
|
|
BEGIN \
|
|
DELETE FROM child_table \
|
|
WHERE child_table.parent_key = :new.parent_key; \
|
|
END
|
|
|
|
#define DEF_FAC_MESSAGE(a,b,c) INSERT INTO fac_message VALUES (a,b)
|
|
|
|
// Default Setup volgens;
|
|
#define DEF_SETUP(n, f) INSERT INTO ini_setup (ini_setup_name, ini_setup_name_full) VALUES (UPPER(n), f)
|
|
|
|
#define UPD_FAC_FUNCTIE2(c, o, m, l, d, g, i) UPDATE fac_functie SET fac_functie_omschrijving=o,fac_functie_module=m,fac_functie_min_level=l, fac_functie_discipline=d, fac_functie_groep=g, fac_functie_info=i) WHERE fac_functie_code=c
|
|
#define DEF_FAC_FUNCTIE3(c, m, l, d, g) INSERT INTO fac_functie (fac_functie_code, fac_functie_omschrijving,fac_functie_module,fac_functie_min_level, fac_functie_discipline, fac_functie_groep, fac_functie_info) VALUES(c, 'lcl_' || c, m, l, d, g, 'lcl_' || c || '_info')
|
|
#define DEF_FAC_FUNCTIE3x(c, x, m, l, d, g) INSERT INTO fac_functie (fac_functie_code, fac_functie_omschrijving,fac_functie_module,fac_functie_min_level, fac_functie_discipline, fac_functie_groep, fac_functie_info) VALUES(c||x, 'lcl_' || c ||x, m, l, d, g, 'lcl_' || c || x ||'_info')
|
|
#define DEF_FAC_SRTNOT(c, m, o, u, g, x, d) INSERT INTO fac_srtnotificatie (fac_srtnotificatie_code, fac_srtnotificatie_mode, fac_srtnotificatie_oms, fac_srtnotificatie_url, fac_srtnotificatie_groep, fac_srtnotificatie_xmlnode, fac_srtnotificatie_delay) VALUES (c, m, o, u, g, x, d)
|
|
|
|
#define DEF_MENUKOP(g, s, i) INSERT INTO fac_menu (fac_menu_volgnr, fac_menu_altlabel, fac_menu_altgroep, fac_menu_image, fac_menu_level) SELECT COALESCE(MAX(fac_menuitems_key),0)*100+50, s, g, i, 1 FROM fac_menuitems
|
|
#define DEF_MENUKOPV(g, v, s, i) INSERT INTO fac_menu (fac_menu_volgnr, fac_menu_altlabel, fac_menu_altgroep, fac_menu_image, fac_menu_level) VALUES (v, s, g, i, 1)
|
|
#define DEF_MENUITEM(g, l, i, m, u, s, d, f, rw, x, img) INSERT INTO fac_menuitems (fac_menuitems_groep, fac_menuitems_label, fac_menuitems_oms, fac_menuitems_module, fac_menuitems_url, fac_menuitems_srtdisc, fac_menuitems_disc, fac_functie_key, fac_menuitems_rw, fac_menuitems_default, fac_menuitems_image) SELECT g, l, i, m, u, s, d, fac_functie_key, rw, x, img FROM fac_functie WHERE fac_functie_code=f
|
|
|
|
#define DEF_MENUENTRY(g, v, l, i, m, u, s, d, f) INSERT INTO fac_menu (fac_menu_volgnr, fac_menu_altlabel, fac_menu_info, fac_menu_alturl, fac_menu_altgroep, fac_menu_level, fac_functie_key) SELECT v, l, i, u, g, 2, fac_functie_key FROM fac_functie WHERE fac_functie_code = f AND NOT EXISTS (SELECT '' FROM fac_menu WHERE fac_menu_alturl = u)
|
|
|
|
#define DEF_SETTING(m, n, d, t) INSERT INTO fac_setting (fac_setting_module, fac_setting_name, fac_setting_default, fac_setting_description) VALUES (m, n, d, t)
|
|
#define DEF_BOOKMARK(x, p, q) INSERT INTO fac_bookmark (fac_bookmark_id, fac_bookmark_naam, fac_bookmark_xmlnode, fac_bookmark_path, fac_bookmark_query) VALUES (DBMS_RANDOM.string ('a', 16), x, x, p, q)
|
|
// We begonnen te denken dat het om statussen ging, maar het werd meer. De naam van de macro bleef echter
|
|
#define DEF_STATUS(x, c, t) INSERT INTO fac_code2label (fac_code2label_domein ,fac_code2label_code, fac_code2label_label) VALUES (x, c, t)
|
|
|
|
// Voeg (nieuwe, default) menukop met label a van groep g en level l toe na bestaande menuoptie met label b, increment n
|
|
#define MENU_INSKOP_AFTER(a, b, g, n) INSERT INTO fac_menu (fac_menu_volgnr, fac_menu_altlabel, fac_menu_altgroep, fac_menu_level) SELECT fac_menu_volgnr + n, a, g, 1 FROM fac_menu m WHERE m.fac_menuitems_key = (SELECT MAX (mm.fac_menuitems_key) FROM fac_menu mm, fac_menuitems mmi WHERE mm.fac_menuitems_key = mmi.fac_menuitems_key AND mmi.fac_menuitems_label = b)
|
|
// Voeg (nieuwe, default) menuoptie met label a toe na de eerste bestaande menukop met label b, increment n
|
|
#define MENU_INS_AFTERKOP(a, b, n) INSERT INTO fac_menu (fac_menuitems_key, fac_menu_altlabel, fac_menu_alturl, fac_menu_altgroep, fac_menu_image, fac_menu_info, fac_menu_volgnr, fac_menu_level) SELECT i.fac_menuitems_key, i.fac_menuitems_label, i.fac_menuitems_url, i.fac_menuitems_groep, i.fac_menuitems_image, i.fac_menuitems_oms, MIN (m.fac_menu_volgnr) + n, 2 FROM fac_menuitems i, fac_menu m WHERE i.fac_menuitems_key = (SELECT MAX (mmi.fac_menuitems_key) FROM fac_menuitems mmi WHERE mmi.fac_menuitems_label = a) AND m.fac_menu_altlabel = b GROUP BY i.fac_menuitems_key
|
|
// Voeg (nieuwe, default) menuoptie met label a toe na bestaande menuoptie met label b, increment n
|
|
#define MENU_INS_AFTER(a, b, n) INSERT INTO fac_menu (fac_menuitems_key, fac_menu_altlabel, fac_menu_alturl, fac_menu_altgroep, fac_menu_image, fac_menu_info, fac_menu_volgnr, fac_menu_level) SELECT i.fac_menuitems_key, i.fac_menuitems_label, i.fac_menuitems_url, i.fac_menuitems_groep, i.fac_menuitems_image, i.fac_menuitems_oms, m.fac_menu_volgnr + n, 2 FROM fac_menuitems i, fac_menu m WHERE i.fac_menuitems_key = (SELECT MAX(mmi.fac_menuitems_key) FROM fac_menuitems mmi WHERE mmi.fac_menuitems_label = a) AND m.fac_menuitems_key = (SELECT MAX(mm.fac_menuitems_key) FROM fac_menu mm, fac_menuitems mmi WHERE mm.fac_menuitems_key = mmi.fac_menuitems_key AND mmi.fac_menuitems_label = b)
|
|
// poging 1
|
|
#define MENU_MOV_AFTER(b, a, n) UPDATE fac_menu SET fac_menu_volgnr = (SELECT m.fac_menu_volgnr + n FROM fac_menuitems i, fac_menu m WHERE m.fac_menuitems_key=i.fac_menuitems_key AND i.fac_menuitems_key = (SELECT MAX(mmi.fac_menuitems_key) FROM fac_menuitems mmi WHERE mmi.fac_menuitems_label = a)) WHERE fac_menuitems_key = (SELECT MAX(mm.fac_menuitems_key) FROM fac_menu mm, fac_menuitems mmi WHERE mm.fac_menuitems_key = mmi.fac_menuitems_key AND mmi.fac_menuitems_label = b)
|
|
|
|
|
|
// Default widget's volgens;
|
|
#define DEF_WIDGET(t, n, c, u, p) INSERT INTO fac_widget (fac_widget_type, fac_widget_naam, fac_widget_content, fac_widget_url, fac_widget_public) VALUES (UPPER(t), n, c, u, p)
|
|
|
|
// NOT_NULL(): Columnconstraint ipv. NOT NULL, De melding ORA-1400: mandatory (NOT NULL)...
|
|
// geeft de constraintnaam niet, zodat de melding niet vertaald kan worden.
|
|
// De CHECK() geeft het wel, dus wordt deze gebruikt.
|
|
#define NOT_NULL(column, constraint_name) CONSTRAINT constraint_name CHECK(column IS NOT NULL)
|
|
|
|
#define DEF_IMPORT2(aut, code, oms_nl, oms_en, csv_typ, charset, folder, files) \
|
|
DECLARE ~\
|
|
imp_key NUMBER(10); ~\
|
|
BEGIN ~\
|
|
INSERT INTO fac_import_app (fac_import_app_code, ~\
|
|
fac_import_app_oms, ~\
|
|
fac_functie_key, ~\
|
|
fac_import_app_csv, ~\
|
|
fac_import_app_charset, ~\
|
|
fac_import_app_folder, ~\
|
|
fac_import_app_files) ~\
|
|
VALUES (UPPER (code), ~\
|
|
oms_nl, ~\
|
|
(SELECT fac_functie_key ~\
|
|
FROM fac_functie ~\
|
|
WHERE fac_functie_code = aut), ~\
|
|
csv_typ, ~\
|
|
charset, ~\
|
|
folder, ~\
|
|
files) ~\
|
|
RETURNING fac_import_app_key INTO imp_key; ~\
|
|
INSERT INTO fac_locale (fac_locale_lang, fac_locale_kolomkeyval, fac_locale_kolomnaam, fac_locale_tekst) ~\
|
|
VALUES ('EN', imp_key, 'FAC_IMPORT_APP_OMS', oms_en); ~\
|
|
END; ~\
|
|
/ ~\
|
|
|
|
#define DEF_IMPORT(aut, code, oms_nl, oms_en, csv_typ) \
|
|
DEF_IMPORT2(aut, code, oms_nl, oms_en, csv_typ, NULL, NULL, NULL) ~\
|
|
|
|
#define DEF_RAPPORT(grp, aut, func, view, oms_nl, info_nl, oms_en, info_en) \
|
|
DECLARE ~\
|
|
rap_key NUMBER(10); ~\
|
|
BEGIN ~\
|
|
INSERT INTO fac_usrrap ~\
|
|
( fac_usrrap_omschrijving, fac_usrrap_view_name ~\
|
|
, fac_usrrap_vraagbegindatum, fac_usrrap_vraageinddatum ~\
|
|
, fac_usrrap_functie, fac_usrrap_autorefresh ~\
|
|
, fac_usrrap_info ~\
|
|
, fac_functie_key, fac_usrrap_groep ~\
|
|
) ~\
|
|
VALUES ( oms_nl, view, 0, 0, func, 0, info_nl ~\
|
|
, (SELECT fac_functie_key FROM fac_functie ~\
|
|
WHERE fac_functie_code = aut ~\
|
|
), grp ~\
|
|
) ~\
|
|
RETURNING fac_usrrap_key INTO rap_key; ~\
|
|
INSERT INTO fac_locale ( fac_locale_lang, fac_locale_kolomkeyval, fac_locale_kolomnaam, fac_locale_tekst) ~\
|
|
VALUES ('EN', rap_key, 'FAC_USRRAP_OMSCHRIJVING', oms_en); ~\
|
|
INSERT INTO fac_locale ( fac_locale_lang, fac_locale_kolomkeyval, fac_locale_kolomnaam, fac_locale_tekst) ~\
|
|
VALUES ('EN', rap_key, 'FAC_USRRAP_INFO', info_en); ~\
|
|
END; ~\
|
|
/ ~\
|
|
|
|
#define DEF_RAPPORT_COL(oms_nl, volgnr, col, f, d, t, v, label_nl, label_en) \
|
|
DECLARE ~\
|
|
col_key NUMBER(10); ~\
|
|
BEGIN ~\
|
|
INSERT INTO fac_usrrap_cols ~\
|
|
( fac_usrrap_key, fac_usrrap_cols_volgnr ~\
|
|
, fac_usrrap_cols_column_name, fac_usrrap_cols_caption ~\
|
|
, fac_usrrap_cols_filter, fac_usrrap_cols_filterdefault ~\
|
|
, fac_usrrap_cols_datatype, fac_usrrap_cols_visible ~\
|
|
) ~\
|
|
VALUES ( (SELECT fac_usrrap_key ~\
|
|
FROM fac_usrrap ~\
|
|
WHERE fac_usrrap_omschrijving = oms_nl ~\
|
|
) ~\
|
|
, volgnr, col, label_nl ~\
|
|
, f, d, t, v ~\
|
|
) ~\
|
|
RETURNING fac_usrrap_cols_key INTO col_key; ~\
|
|
/* INSERT INTO fac_locale ( fac_locale_lang, fac_locale_kolomkeyval, fac_locale_kolomnaam, fac_locale_tekst) */ ~\
|
|
/* VALUES ('EN', col_key, 'FAC_USRRAP_COLS_CAPTION', label_en); */ ~\
|
|
END; ~\
|
|
/ ~\
|
|
|
|
#define START_LOCALEITEMS(dialect_id) \
|
|
DECLARE ~ \
|
|
v_dialect_id fac_localeitems.fac_localeitems_dialect_id%TYPE; ~ \
|
|
BEGIN ~ \
|
|
v_dialect_id := dialect_id; ~ \
|
|
insert into fac_localeitems ~ \
|
|
(fac_localeitems_dialect_id, fac_localeitems_lcl) ~ \
|
|
SELECT * FROM (
|
|
|
|
|
|
#define DEFINE_LOCALEITEM(lcl) \
|
|
SELECT v_dialect_id, STRINGIZE(lcl) FROM DUAL UNION ALL
|
|
|
|
#define END_LOCALEITEMS() \
|
|
SELECT 'a', 'b' FROM DUAL WHERE 1=0) ~ \
|
|
MINUS select fac_localeitems_dialect_id, fac_localeitems_lcl FROM fac_localeitems; ~ \
|
|
END; ~\
|
|
/ ~ \
|
|
COMMIT;
|
|
|
|
#define AUDIT_BEGIN(table) ~\
|
|
CREATE OR REPLACE TRIGGER aud_t_##table## ~\
|
|
AFTER INSERT OR UPDATE OR DELETE ON table ~\
|
|
FOR EACH ROW ~\
|
|
DECLARE ~\
|
|
action VARCHAR2(1); ~\
|
|
BEGIN ~\
|
|
IF DELETING THEN ~\
|
|
action := 'D'; ~\
|
|
ELSIF INSERTING THEN ~\
|
|
action := 'I'; ~\
|
|
ELSE ~\
|
|
action := 'U'; ~\
|
|
END IF;
|
|
|
|
#define AUDIT_VALUE_K(table, tablekey, column) ~\
|
|
aud.auditaction(STRINGIZE(table), ~\
|
|
:old.tablekey, ~\
|
|
:new.tablekey, ~\
|
|
STRINGIZE(column), ~\
|
|
:old.column, ~\
|
|
:new.column, ~\
|
|
action);
|
|
|
|
#define AUDIT_VALUE_T(table, tablekey, column) ~\
|
|
aud.auditaction(STRINGIZE(table), ~\
|
|
:old.tablekey, ~\
|
|
:new.tablekey, ~\
|
|
STRINGIZE(column), ~\
|
|
:old.column.tijdsduur||' '||:old.column.eenheid, ~\
|
|
:new.column.tijdsduur||' '||:new.column.eenheid, ~\
|
|
action);
|
|
|
|
#define AUDIT_VALUE(table, column) ~\
|
|
AUDIT_VALUE_K(table, table##_key, column)
|
|
|
|
#define AUDIT_VAL_T(table, column) ~\
|
|
AUDIT_VALUE_T(table, table##_key, column)
|
|
|
|
#define AUDIT_END() ~\
|
|
END; ~\
|
|
/ ~ \
|
|
|
|
|
|
#endif // _COMSQL_H
|