The coding of triggers is dependent on whether you are using DB2/LUW or
DB2/zOS. LUW has some features that are not yet available on the
mainframe.
In your specific procedure, columns values selected in the EXIST statement
are not made available for use within the BEGIN ATOMIC statement.
Therefore, within your UPDATE statement, the SET for
rpm_with_trng_skl_lvl=code_value has no idea of what code_value is.
If I were to code this on the DB2/LUW system I work on, I would totally
eliminate the EXIST as you have written it, and place all checking within
the BEGIN ATOMIC. You could still use the SELECT followed by an IF
statement that processes the UPDATE if the returned SQLCODE from the SELECT
= 0 .
Al Glinter, Lead Database Administrator
Federated Systems Group
"SangeethaPriya
R. "
<db2udbdba-ezmlms To
hield-x45220525.x "LazyDBA Discussion"
[Email Address Removed] <[Email address protected]
A.com> cc
03/23/2007 05:10 Subject
AM Reg. Triggers
Hi Friends,
CREATE TRIGGER trng1_update
AFTER UPDATE OF trng_skill_level_id ON training
REFERENCING OLD AS o NEW AS n OLD TABLE AS ot FOR EACH ROW MODE DB2SQL
WHEN( EXISTS
( SELECT trng_id,trng_skill_level_id,code_value FROM ot, code_misc WHERE
trng_id = n.trng_id and code_id=n.trng_skill_level_id ) )
BEGIN ATOMIC UPDATE replacements
SET rpm_with_trng_skl_lvl_id = n.trng_skill_level_id,
rpm_with_trng_skl_lvl=code_value WHERE rpm_with_trng_id = n.trng_id;
END
When I Execute the above trigger its shows the following error.
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0206N "CODE_VALUE" is not valid in the context where it is used.
LINE NUMBER=1. SQLSTATE=42703
Please help me to solve this issue.
Thanks and Best Regards,
Sangeetha Priya R.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page