SEO

#1 (**permalink**) 02-26-2008, 06:22 PM

I have an application written for version 8 that uses an INSTEAD OF
trigger, and I need to convert it to support someone that needs it for
v7. Is there a way to achieve similar functionality in v7. Here's an
example -

View on Base Table -
CREATE VIEW BASE_TABLE_VIEW AS SELECT
col1,
myUDF(col2),
col3)
FROM BASE_TABLE

Trigger on Base Table -
CREATE TRIGGER BASE_TABLE_TRGI NO CASCADE BEFORE INSERT ON
BASE_TABLE REFERENCING NEW AS n FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
DECLARE new_name varchar(50);

SET new_name = myOtherUDF(col2);
SET n.col2 = new_name;
END

If I try to insert into the view, I get an error -
SQL0151N The column "COL2" cannot be updated. SQLSTATE=42808

Of course, if I update the base table directly, it works fine, but if
possible I'd like to avoid updating the application code, which I
don't have access to, and just modify views, triggers etc. that I do
have access to.

Thanks!

#2 (**permalink**) 02-26-2008, 06:23 PM

Without App impact? If there were one there would be no INSTEAD OF
triggers in V8 :-)
The primary reason to provide insetad-of triggers was for
encryption/decryption. Which is exactly the scenario you seem to describe.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

#3 (**permalink**) 02-26-2008, 06:23 PM

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<caj6ti$t6n$1@hanover.torolab.ibm.com>...
> Without App impact? If there were one there would be no INSTEAD OF
> triggers in V8 :-)
> The primary reason to provide insetad-of triggers was for
> encryption/decryption. Which is exactly the scenario you seem to describe.
>
> Cheers
> Serge

Serge,

Thanks again for your help. Yes, I am using it for encryption and
decryption, and was just curious if anyone had come up with any type
of ingenious workaround.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

V7 Alternative to INSTEAD OF Trigger?