|
DB2 Example: Trigger with Update
/*=======================================================*/
/* This program is intended to illustrate an after update*/
/* trigger specified with ALWREPCHG(*YES) where the */
/* trigger will update the record a second time. */
/* */
/* This program will only work on files that do not */
/* contain null values. If a field contains null values, */
/* SQL must be used to update the file. */
/* */
/* In V3R2 and V3R7, a much faster mechanism is to */
/* simply update the after trigger buffer image. */
/* */
/* */
/* CRTSQLCI OBJ(QTEMP/A8) SRCFILE(MJASRC/C) */
/* COMMIT(*NONE) OUTPUT(*PRINT) */
/* USRPRF(*OWNER) DYNUSRPRF(*OWNER) */
/* TGTRLS(V3R1M0) GENLVL(40) */
/* */
/* CRTCMOD MODULE(CORPDATA/TRG04) SRCFILE(QTEMP/QSQLTEMP)*/
/* OUTPUT(*PRINT) */
/* OPTION(*SHOWINC) DBGVIEW(*ALL) */
/* */
/* CRTPGM PGM(CORPDATA/TRG04) MODULE(CORPDATA/TRG04) */
/* ACTGRP(*CALLER) USRPRF(*OWNER) */
/* */
/* */
/*=======================================================*/
#include
#include
#include
/*=======================================================*/
/* Definition of the structure passed as the first */
/* parameter from database to the trigger program. */
/* The include is used so that any additional fields */
/* in the interface template in the future will be */
/* brought into the program if it is recompiled. */
/* */
/* The includes in the QSYSINC library must be on */
/* your system to compile this program. Option 13 of */
/* the OS/400 install will install them. Also */
/* SYSINC(*YES) (the default) must be specified on */
/* CRTBNDC or CRTCMOD. */
/* */
/*=======================================================*/
#include
Qdb_Trigger_Buffer_t *TrgBuffer;
/*=======================================================*/
/* This structure provides the subfields of the record */
/* images. Externally defined data structures are used */
/* so a recompile of the program will always pick up the */
/* latest field definitions. */
/*=======================================================*/
#pragma mapinc ("emp", "CORPDATA/EMPLOYEE(*ALL)",\
"both", "d z _P", " ")
#include "emp"
CORPDATA_EMPLOYEE_EMPLOYEE_both_t local_emp;
/*=======================================================*/
/* These pointers are used to point to the before */
/* and after images. The before and after images */
/* are passed in the first parameter structure. */
/*=======================================================*/
CORPDATA_EMPLOYEE_EMPLOYEE_both_t *before_image;
CORPDATA_EMPLOYEE_EMPLOYEE_both_t *after_image;
main(int argc, char**argv)
{
const auto short i1;
static short first_time;
char SQLSTATE�6Ù;
/* Set addressability to the trigger interface buffer */
TrgBuffer = (Qdb_Trigger_Buffer_t *) argv�1Ù;
/* Set addressability to the before and after images */
before_image = (CORPDATA_EMPLOYEE_EMPLOYEE_both_t *)
((char *) TrgBuffer + TrgBuffer->Old_Record_Offset);
after_image = (CORPDATA_EMPLOYEE_EMPLOYEE_both_t *)
((char *) TrgBuffer + TrgBuffer->New_Record_Offset);
local_emp.SALARY = after_image->SALARY;
if (first_time == 0) {
first_time = 1;
/*=======================================================*/
/* Prior to updating the table, we should typically */
/* run under the commitment control isolation level */
/* of the triggering program. There are two key ways */
/* to do this with SQL...SET TRANSACTION or statement */
/* level isolation level (Statement level isolation */
/* was added in V3R6 and V3R2). */
/*=======================================================*/
if (TrgBuffer->Commit_Lock_Level�1Ù == '0') {
EXEC SQL UPDATE corpdata/employee
SET salary = :local_emp.SALARY + 1000
WHERE empno = '111111' WITH NC;
}
else {
if (TrgBuffer->Commit_Lock_Level�1Ù == '1') {
EXEC SQL UPDATE corpdata/employee
SET salary = :local_emp.SALARY + 1000
WHERE empno = '111111' WITH UR;
}
else {
if (TrgBuffer->Commit_Lock_Level�1Ù == '2') {
EXEC SQL UPDATE corpdata/employee
SET salary = :local_emp.SALARY + 1000
WHERE empno = '111111' WITH CS;
}
else {
if (TrgBuffer->Commit_Lock_Level�1Ù == '3') {
EXEC SQL UPDATE corpdata/employee
SET salary = :local_emp.SALARY + 1000
WHERE empno = '111111' WITH RS;
}
else {
EXEC SQL UPDATE corpdata/employee
SET salary = :local_emp.SALARY + 1000
WHERE empno = '111111' WITH RR;
}
}
}
}
first_time = 0;
}
else
first_time = 0;
}
| 
