***********************************************************************
** Licensed Materials - Property of IBM
**
** Governed under the terms of the International
** License Agreement for Non-Warranted Sample Code.
**
** (C) COPYRIGHT International Business Machines Corp. 1995 - 2002
** All Rights Reserved.
**
** US Government Users Restricted Rights - Use, duplication or
** disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
***********************************************************************
**
** SOURCE FILE NAME: trigsql.sqb
**
** SAMPLE: How to use a trigger on a table
**
** SQL STATEMENTS USED:
** BEGIN DECLARE SECTION
** END DECLARE SECTION
** EXECUTE IMMEDIATE
** CREATE TABLE
** PREPARE
** CONNECT
** DECLARE
** CLOSE
** DROP
** OPEN
**
** OUTPUT FILE: trigsql.out (available in the online documentation)
***********************************************************************
**
** For more information on the sample programs, see the README file.
**
** For information on developing COBOL applications, see the
** Application Development Guide.
**
** For information on using SQL statements, see the SQL Reference.
**
** For the latest information on programming, compiling, and running
** DB2 applications, visit the DB2 application development website:
** http://www.software.ibm.com/data/db2/udb/ad
***********************************************************************
Identification Division.
Program-ID.
"trigsql".
Data Division.
Working-Storage Section.
copy
"sql.cbl".
copy
"sqlca.cbl".
EXEC SQL BEGIN DECLARE
SECTION END-EXEC.
01 userid
pic x(8).
01 passwd.
49 passwd-length
pic s9(4)
comp-5 value 0.
49 passwd-name
pic x(18).
01 st
pic x(80).
01 st2
pic x(80).
01 symbol
pic x(3).
01 curr-quote
pic s9(8)v9(2)
comp-3.
01 curr-status.
49 curr-status-length
pic s9(4)
comp-5 value 15.
49 curr-status-name
pic x(15).
01 timestamp
pic x(26).
EXEC SQL END DECLARE
SECTION END-EXEC.
77 disp-quote
pic z(8).99
usage display.
77 errloc
pic x(80).
77 counter
pic s9(4)
comp-5 value 0.
Procedure Division.
Main
Section.
display "Sample COBOL program: TRIGSQL".
display " ".
* Get database connection information.
display "Enter your user id (default none): "
with no advancing.
accept userid.
if userid =
spaces
EXEC SQL CONNECT
TO sample
END-EXEC
else
display "Enter your password : " with no advancing
accept passwd-name.
* Passwords in a CONNECT statement must be entered in a VARCHAR format
* with the length of the input string.
inspect passwd-name tallying passwd-length
for characters
before initial " ".
display " ".
EXEC SQL CONNECT
TO sample USER :userid
USING :passwd
END-EXEC.
move "CONNECT TO" to errloc.
call "checkerr" using SQLCA errloc.
* drop TABLES and TRIGGERS from previous executions of this program.
EXEC SQL DROP TABLE currentquote
END-EXEC.
EXEC SQL DROP TABLE quotehistory
END-EXEC.
EXEC SQL DROP TRIGGER stock_status
END-EXEC.
EXEC SQL DROP TRIGGER record_history
END-EXEC.
* create TABLES and TRIGGERS needed for this program.
EXEC SQL CREATE TABLE currentquote (symbol CHAR(3)
NOT NULL,
quote DECIMAL(6,2),
status VARCHAR(8))
END-EXEC.
move "CREATE TABLE CURRENTQUOTE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CREATE TABLE quotehistory (symbol CHAR(3)
NOT NULL,
quote DECIMAL(6,2), timestamp TIMESTAMP)
END-EXEC.
move "CREATE TABLE QUOTEHISTORY" to errloc.
call "checkerr" using SQLCA errloc.
move "INSERT INTO currentquote VALUES ('IBM', 68.0, NULL)"
to st.
EXEC SQL EXECUTE IMMEDIATE :st
END-EXEC.
move "INSERT INTO CURRENTQUOTE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CREATE TRIGGER STOCK_STATUS
NO CASCADE
BEFORE UPDATE
OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE
FOR EACH ROW
MODE DB2SQL
SET NEWQUOTE.
STATUS =
CASE
WHEN NEWQUOTE.
QUOTE >=
(
SELECT MAX(
QUOTE)
FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP)=YEAR(CURRENT
DATE))
THEN 'High'
WHEN NEWQUOTE.
QUOTE <=
(
SELECT MIN(
QUOTE)
FROM QUOTEHISTORY
WHERE SYMBOL = NEWQUOTE.SYMBOL
AND YEAR(TIMESTAMP)=YEAR(CURRENT
DATE))
THEN 'Low'
WHEN NEWQUOTE.
QUOTE > OLDQUOTE.
QUOTE
THEN 'Rising'
WHEN NEWQUOTE.
QUOTE < OLDQUOTE.
QUOTE
THEN 'Dropping'
WHEN NEWQUOTE.
QUOTE = OLDQUOTE.
QUOTE
THEN 'Steady'
END END-EXEC.
move "CREATE TRIGGER STOCK_STATUS" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL CREATE TRIGGER RECORD_HISTORY
AFTER UPDATE
OF QUOTE ON CURRENTQUOTE
REFERENCING NEW AS NEWQUOTE
FOR EACH ROW
MODE DB2SQL
INSERT INTO QUOTEHISTORY
VALUES (NEWQUOTE.SYMBOL,NEWQUOTE.
QUOTE,
CURRENT TIMESTAMP)
END-EXEC.
move "CREATE TRIGGER RECORD_HISTORY" to errloc.
call "checkerr" using SQLCA errloc.
* main section of the program - activation of the triggers
move "SELECT * from currentquote" to st2.
move "UPDATE currentquote SET quote=? WHERE symbol='IBM'"
to st.
EXEC SQL PREPARE QUPDATE
FROM :st
END-EXEC.
move 68.25
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move 68.75
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move 68.5
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move 68.5
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move 68.62
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move 68
to curr-quote.
perform Fetch-Info thru End-Fetch-Info.
move "SELECT * FROM quotehistory" to st.
EXEC SQL PREPARE S2
FROM :st
END-EXEC.
EXEC SQL DECLARE C2 CURSOR
FOR S2
END-EXEC.
EXEC SQL OPEN C2
END-EXEC.
display " ".
display "Contents of the 'quotehistory' table after updates".
perform Quote-History thru End-Quote-History
until SQLCODE
not equal 0.
EXEC SQL CLOSE C2
END-EXEC.
EXEC SQL CONNECT
RESET END-EXEC.
move "CONNECT RESET" to errloc.
call "checkerr" using SQLCA errloc.
End-Main.
go to End-Prog.
Fetch-Info
Section.
* Need to use a cursor, and dynamically select from the tables since the
* tables may not exist prior to the execution of the program.
move space to curr-status.
EXEC SQL EXECUTE QUPDATE
USING :curr-quote
END-EXEC.
move "UPDATE" to errloc.
call "checkerr" using SQLCA errloc.
EXEC SQL PREPARE S1
FROM :st2
END-EXEC.
EXEC SQL DECLARE C1 CURSOR
FOR S1
END-EXEC.
EXEC SQL OPEN C1
END-EXEC.
EXEC SQL FETCH C1
INTO :symbol, :curr-quote, :curr-status
END-EXEC.
EXEC SQL CLOSE C1
END-EXEC.
move curr-quote
to disp-quote.
display symbol,
" ", disp-quote,
" ", curr-status-name.
End-Fetch-Info.
exit.
Quote-History
Section.
EXEC SQL FETCH C2
INTO :symbol, :curr-quote, :timestamp
END-EXEC.
if SQLCODE
not equal 0
go to End-Quote-History.
move curr-quote
to disp-quote.
display symbol,
" ", disp-quote,
" ", timestamp.
End-Quote-History.
exit.
End-Prog.
stop run.
