Expertise needed on trigger

[Ziozio]

I facing this scenario. I have a table in Oracle8i that has some columns encrypted. I was looking at creating a trigger that can do native decryption in the output generated when a particular user "select" from this table. However, trigger doesn't accept "select" clause. Anyone has any idea or alternatives on how to implement this cleanly?

 

[MikeLacey]

Ok...

You could retrieve data from the table using a stored procedure, you would pass this SP a WHERE clause and it would return a row from a cursor each time it was called...

If that makes sense.
Mike
michael.j.lacey@ntlworld.com

 

[prosenjit]

ZioZio,
Triggers never come into the picture in scenarios like yours.
Write a function that shall do the decryption after selecting the encrypted column fro your table.You return this decrypted value from the function.

Then use this select function in the select statement.
Here is an example:

CREATE TABLE AA
(
encr_column number
)
/

INSERT INTO AA /*Assume that 111 is the encrypted value
VALUES(111); and the decrypted algorithm is column
value + the value itself. So the decrypted
value in this case would be 111+111 =
222.*/


commit;

CREATE OR REPLACE FUNCTION decrypt(val NUMBER) RETURN NUMBER
AS
BEGIN
RETURN val + val;
END;
/

/* Now you want to write a select statement that shall display the decrypted value from the table which contains the encrypted value Is that correct??????*/

SQL>SELECT 'The decrypted value is'||' '||decrypt(encr_column) from AA ;

The output is 222


Hope this solves your problem once and for all.

Bye....

 

[prosenjit]

Did it work buddy!!!!

Prosenjit