Trigger manuell anlegen

CREATE TRIGGER F_BDAT__INS_TRI FOR DBADMIN.F_BDAT
AFTER INSERT EXECUTE
(
VAR NEXTID INTEGER;
TRY
/* Sort Wert wird gleich der eingefügten ID gesetzt wenn BD_SORT 0 oder NULL ist
F_SEQUENCE wird aktualisiert, wenn der neue BD_ID Wert höher ist als der alte
Wert in F_SEQUENCE
*/
IF NEW.BD_SORT IS NULL OR NEW.BD_SORT=0 THEN
UPDATE DBADMIN.F_BDAT SET BD_SORT = :NEW.BD_ID WHERE BD_ID = :NEW.BD_ID IGNORE TRIGGER;
IF NEW.BD_TIME IS NULL THEN
UPDATE DBADMIN.F_BDAT SET BD_TIME = NOW() WHERE BD_ID = :NEW.BD_ID IGNORE TRIGGER;
UPDATE DBADMIN.F_BDAT SET BD_MTIME = NOW(), BD_MCOUNT = 0 WHERE BD_ID = :NEW.BD_ID IGNORE TRIGGER;
SELECT SEQ_NEXTID FROM DBADMIN.F_SEQUENCE WHERE SEQ_NAME = 'F_BDAT';
IF $RC=0 THEN
BEGIN
FETCH INTO :NEXTID;
IF NEXTID <= NEW.BD_ID THEN
UPDATE DBADMIN.F_SEQUENCE SET SEQ_NEXTID = :NEW.BD_ID + 1 WHERE SEQ_NAME = 'F_BDAT';
END
ELSE
INSERT INTO DBADMIN.F_SEQUENCE (SEQ_NAME, SEQ_NEXTID) VALUES ('F_BDAT', :NEW.BD_ID + 1);
CATCH
IF $RC <> 100 THEN
STOP ($RC, 'F_BDAT__INS_TRI trigger error');
)
//
CREATE TRIGGER F_BDAT__UPD_TRI FOR DBADMIN.F_BDAT
AFTER UPDATE EXECUTE
(
TRY
IF NEW.BD_SORT IS NULL OR NEW.BD_SORT=OLD.BD_SORT THEN
BEGIN
INSERT INTO "DBADMIN"."ARC__F_BDAT" (
MTIME, MBY, MST,
BD_ID, BD_PA_ID, BD_DAT, BD_ART, BD_TEXT, BD_PRIORITAET,
BD_OU_ID, BD_EIN_DAT, BD_ITEXT, BD_OA_ID, BD_AZ_ID, BD_ACCESS,
BD_XA_ID, BD_TIME, BD_EP_ID, BD_SORT, BD_SEND_STATUS, BD_NETZ_STATUS,
BD_NETZ_NOTFALL, BD_ANHANG, BD_WORKFLOW, BD_ICON1, BD_ICON2, BD_RANG,
BD_INFOEINTRAG, BD_CTIME, BD_CBY, BD_MTIME, BD_MBY, BD_MCOUNT,
BD_OZ_ID, BD_PROTECTED
) VALUES (
NOW(), :NEW.BD_MBY, :NEW.BD_OZ_ID,
:OLD.BD_ID, :OLD.BD_PA_ID, :OLD.BD_DAT, :OLD.BD_ART, :OLD.BD_TEXT, :OLD.BD_PRIORITAET,
:OLD.BD_OU_ID, :OLD.BD_EIN_DAT, :OLD.BD_ITEXT, :OLD.BD_OA_ID, :OLD.BD_AZ_ID, :OLD.BD_ACCESS,
:OLD.BD_XA_ID, :OLD.BD_TIME, :OLD.BD_EP_ID, :OLD.BD_SORT, :OLD.BD_SEND_STATUS, :OLD.BD_NETZ_STATUS,
:OLD.BD_NETZ_NOTFALL, :OLD.BD_ANHANG, :OLD.BD_WORKFLOW, :OLD.BD_ICON1, :OLD.BD_ICON2, :OLD.BD_RANG,
:OLD.BD_INFOEINTRAG, :OLD.BD_CTIME, :OLD.BD_CBY, :OLD.BD_MTIME, :OLD.BD_MBY, :OLD.BD_MCOUNT,
:OLD.BD_OZ_ID, :OLD.BD_PROTECTED
);
IF NEW.BD_MCOUNT IS NULL THEN
UPDATE DBADMIN.F_BDAT SET BD_MCOUNT = 1, BD_MTIME = NOW() WHERE BD_ID = :OLD.BD_ID IGNORE TRIGGER
ELSE
UPDATE DBADMIN.F_BDAT SET BD_MCOUNT = :OLD.BD_MCOUNT + 1, BD_MTIME = NOW() WHERE BD_ID = :OLD.BD_ID IGNORE TRIGGER;
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'F_BDAT__UPD_TRI trigger error');
)
//
CREATE TRIGGER F_BDAT__DEL_TRI FOR DBADMIN.F_BDAT
AFTER DELETE EXECUTE
(
TRY
BEGIN
INSERT INTO "DBADMIN"."ARC__F_BDAT" (
MTIME, MBY, MST,
BD_ID, BD_PA_ID, BD_DAT, BD_ART, BD_TEXT, BD_PRIORITAET,
BD_OU_ID, BD_EIN_DAT, BD_ITEXT, BD_OA_ID, BD_AZ_ID, BD_ACCESS,
BD_XA_ID, BD_TIME, BD_EP_ID, BD_SORT, BD_SEND_STATUS, BD_NETZ_STATUS,
BD_NETZ_NOTFALL, BD_ANHANG, BD_WORKFLOW, BD_ICON1, BD_ICON2, BD_RANG,
BD_INFOEINTRAG, BD_CTIME, BD_CBY, BD_MTIME, BD_MBY, BD_MCOUNT,
BD_OZ_ID, BD_PROTECTED
) VALUES (
NOW(), :OLD.BD_MBY, :OLD.BD_OZ_ID,
:OLD.BD_ID, :OLD.BD_PA_ID, :OLD.BD_DAT, :OLD.BD_ART, :OLD.BD_TEXT, :OLD.BD_PRIORITAET,
:OLD.BD_OU_ID, :OLD.BD_EIN_DAT, :OLD.BD_ITEXT, :OLD.BD_OA_ID, :OLD.BD_AZ_ID, :OLD.BD_ACCESS,
:OLD.BD_XA_ID, :OLD.BD_TIME, :OLD.BD_EP_ID, :OLD.BD_SORT, :OLD.BD_SEND_STATUS, :OLD.BD_NETZ_STATUS,
:OLD.BD_NETZ_NOTFALL, :OLD.BD_ANHANG, :OLD.BD_WORKFLOW, :OLD.BD_ICON1, :OLD.BD_ICON2, :OLD.BD_RANG,
:OLD.BD_INFOEINTRAG, :OLD.BD_CTIME, :OLD.BD_CBY, :OLD.BD_MTIME, :OLD.BD_MBY, :OLD.BD_MCOUNT,
:OLD.BD_OZ_ID, :OLD.BD_PROTECTED
);
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'F_BDAT__DEL_TRI trigger error');
)
//
CREATE TRIGGER F_BD_LONGTEXT__INS_TRI FOR DBADMIN.F_BD_LONGTEXT
AFTER INSERT EXECUTE
(
VAR NEXTID INTEGER;
TRY
SELECT SEQ_NEXTID FROM DBADMIN.F_SEQUENCE WHERE SEQ_NAME = 'F_BD_LONGTEXT';
IF $RC=0 THEN
BEGIN
FETCH INTO :NEXTID;
IF NEXTID <= NEW.BDL_ID THEN
UPDATE DBADMIN.F_SEQUENCE SET SEQ_NEXTID = :NEW.BDL_ID + 1 WHERE SEQ_NAME = 'F_BD_LONGTEXT';
END
ELSE
INSERT INTO DBADMIN.F_SEQUENCE (SEQ_NAME, SEQ_NEXTID) VALUES ('F_BD_LONGTEXT', :NEW.BDL_ID + 1);
CATCH
IF $RC <> 100 THEN
STOP ($RC, 'F_BD_LONGTEXT__INS_TRI trigger error');
)
//
CREATE TRIGGER F_BD_LONGTEXT__UPD_TRI FOR DBADMIN.F_BD_LONGTEXT
AFTER UPDATE EXECUTE
(
TRY
BEGIN
INSERT INTO "DBADMIN"."ARC__F_BD_LONGTEXT" (
MTIME, MBY, MST,
BDL_ID, BDL_BD_ID, BDL_TEXT
) VALUES (
NOW(), 0, 0,
:OLD.BDL_ID, :OLD.BDL_BD_ID, :OLD.BDL_TEXT
);
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'F_BD_LONGTEXT__UPD_TRI trigger error');
)
//
CREATE TRIGGER F_BD_LONGTEXT__DEL_TRI FOR DBADMIN.F_BD_LONGTEXT
AFTER DELETE EXECUTE
(
TRY
BEGIN
INSERT INTO "DBADMIN"."ARC__F_BD_LONGTEXT" (
MTIME, MBY, MST,
BDL_ID, BDL_BD_ID, BDL_TEXT
) VALUES (
NOW(), 0, 0,
:OLD.BDL_ID, :OLD.BDL_BD_ID, :OLD.BDL_TEXT
);
END;
CATCH
IF $RC <> 100
THEN STOP ($RC, 'F_BD_LONGTEXT__DEL_TRI trigger error');
)