ERROR SQL0818 appear after rolled back revalidation of an SQL
routine or trigger
Consider the following example.
1) Update trigger TRIG1 becomes invalid due to transfer owner
select trigname, valid from syscat.triggers where
trigname='TRIG1'
TRIGNAME VALID
---------- ----
TRIG1 Y
select pkgname, valid from syscat.packages where pkgname in
(select bname from syscat.trigdep where trigname='TRIG1')
PKGNAME VALID
--------- ----
P441013123 Y
TRANSFER OWNERSHIP OF TABLE DUMMYSCH.TEST to user TESTUSR
PRESERVE PRIVILEGES
select trigname, valid from syscat.triggers where
trigname='TRIG1'
TRIGNAME VALID
---------- ----
TRIG1 N
select pkgname, valid from syscat.packages where pkgname in
(select bname from syscat.trigdep where trigname='TRIG1')
PKGNAME VALID
--------- ----
P441013123 Y
2) An update statement is issued that triggers the trigger.
This should cause revalidation, but the statement itself fails
due to some expected error. E.g. SQL0438N.
The revalidation is rolled back.
db2 "update DUMMYSCH.TEST set f1=0"
SQL0438N Application raised error or warning with diagnostic
text: "Trying to update: forbidden". SQLSTATE=75002
select trigname, valid from syscat.triggers where
trigname='TRIG1'
TRIGNAME VALID
---------- ----
TRIG1 N
select pkgname, valid from syscat.packages where pkgname in
(select bname from syscat.trigdep where trigname='TRIG1')
PKGNAME VALID
--------- ----
P441013123 Y
3) The trigger is revalidated again on a new statement, but the
update
statement returns a -818 sqlcode error
db2 "update DUMMYSCH.TEST1 set f1=0"
SQL0723N An error occurred in a triggered SQL statement in
trigger
"DUMMYSCH.TEST". Information returned for the error includes
SQLCODE "-818",
SQLSTATE "51003" and message tokens "". SQLSTATE=09000 |
