DataBase Triggers
DOWNLOAD
Download text files
A database trigger is a predefined event that runs
automatically whenever a specified action is performed
on a physical file. A trigger program carries out this
predefined event. A trigger program can be written
in languages such as RPG, COBOL, or C; in SQL; or in CL.
Commands used with triggers
Four main components
- A DB2 table
- Trigger on event
- insert - record written to table
- delete - record deleted from table
- update - record changed in table
- trigger time
- before - Run trigger program before opperating system(NOT!)
- after - Run after table transaction
- trigger program(see below)
Triggers have record level scope will be activated
whenever the event occurs Exception: Update triggers
*ALWAYS - If program opens as update fire trigger
*CHANGE - Only fire trigger if data has been changed
Trigger programs are associated with physical files.
As implemented in DB2 UDB, they cannot be assigned to individual fields.
Below is an example of a trigger program.
There is also a read trigger available.(not shown in example)
You can also use the following program to return the name of the program that
initiated the trigger.
Download text files
***********************************************************************
H DEBUG
*----------------------------------------------------------------
FAUDIT O E K DISK
*----------------------------------------------------------------
D Buffer DS 32767
D FileName 1 10
D LibraryName 11 20
D MemberName 21 30
D TrgEvent 31 31
D TrgTime 32 32
D CommitLckLvl 33 33
D Filler1 34 36
D CCSID 37 40B 0
D Filler2 41 48
D OldOff 49 52B 0
D OldLen 53 56B 0
D OldNullOffset 57 60B 0
D OldNullLength 61 64B 0
D NewOff 65 68B 0
D NewLen 69 72B 0
D NewNullOffset 73 76B 0
D NewNullLength 77 80B 0
D Resv3 81 96
*----------------------------------------------------------------
* Total Buffer length
D BufLen DS
D Leng 1 4B 0
*----------------------------------------------------------------
* Date and Time
D TimeDate DS
D TimeDate14 1 14 0
D CurrTime 1 6 0
D CurrDate 7 14 0
D CurrMonth 7 8 0
D CurrDay 9 10 0
D CurrYear 11 14 0
D CYMD DS
D CYMDDate 1 8 0
D CYMDYear 1 4 0
D CYMDMonth 5 6 0
D CYMDDay 7 8 0
*----------------------------------------------------------------
* Map the fields from the DB file
* to take the before/after images apart.
D RcdFmt E DS EXTNAME(ITMTYP)
*----------------------------------------------------------------
* Work fields
D Start s 5P 0
*----------------------------------------------------------------
C *entry Plist
C Buffer Parm Buffer
C BufLen Parm BufLen
*----------------------------------------------------------------
* Determine trigger event and take appropriate action
C Select
*
C When TrgEvent = '1'
C Exsr CheckInsert
*
C When TrgEvent = '2'
C Exsr CheckDelete
*
C When TrgEvent = '3'
C Exsr CheckUpdate
*
C Endsl
*
C Return
*----------------------------------------------------------------
C CheckInsert Begsr
*
C Exsr SetAudFields
*
C Exsr GetNewFields
*
C Endsr
*----------------------------------------------------------------
C CheckDelete Begsr
*
C Exsr SetAudFields
*
C Exsr GetOldFields
*
C Endsr
*----------------------------------------------------------------
C CheckUpdate Begsr
*
C Exsr SetAudFields
*
C Exsr GetOldFields
*
C Exsr GetNewFields
*
C Endsr
*----------------------------------------------------------------
C SetAudFields Begsr
* Date and Time
C Time TimeDate14
C Eval CYMDYear = CurrYear
C Eval CYMDMonth = CurrMonth
C Eval CYMDDay = CurrDay
C Eval AUDDAT = CYMDDate
C Eval AUDTIM = CurrTime
*
C Endsr
*----------------------------------------------------------------
C GetOldFields Begsr
* "Before" image
C Move 'B' AUDSEQ
* move original record to field layout
C Eval Start = OldOff + 1
C Eval RcdFmt = %subst(Buffer:Start:OldLen)
*
* Get fields from "old" record that we need
C Exsr GetFieldData
*
C Write AUDITR
*
C Endsr
*----------------------------------------------------------------
C GetNewFields Begsr
* "After" image
C Move 'A' AUDSEQ
*
* move new record to field layout
C Eval Start = NewOff + 1
C Eval RcdFmt = %subst(Buffer:Start:NewLen)
*
* Get fields from "new" record that we need
C Exsr GetFieldData
*
C Write AUDITR
*
C Endsr
*----------------------------------------------------------------
C GetFieldData Begsr
* Extract fields from data structure
C Eval AUDNBR = VNDNBR
C Eval AUDNAM = VNDNAM
C Eval AUDAD1 = VNDAD1
C Eval AUDAD2 = VNDAD2
C Eval AUDCTY = VNDCTY
C Eval AUDST = VNDST
C Eval AUDZIP = VNDZIP
C Eval AUDCTN = VNDCTN
C Eval AUDPHN = VNDPHN
C Eval AUDPGM = VNDPGM
*
C Endsr
*----------------------------------------------------------------
