ORDS, SODA & JSON in the Database

Terry - You need to create a database trigger on the table. The trigger would reference your sequence.

For example:

 

create or replace trigger T_biu_client
    before insert or update on client
    for each row
declare
  l_new_id number;
begin
    if inserting and :new.id is null then
         select ID2_SEQ.NEXTVAL into l_new_id from dual;
        :new.id := l_new_id;
    end if;
end;

Scott

Hi Scott,

I did some exploring and found a post about triggers. I created the following:
create or replace TRIGGER encrypt_pw_t1
BEFORE INSERT on encrypt_pw
FOR EACH ROW
BEGIN
IF :new.pw_id IS NULL
THEN SELECT pw_id2_seq.nextval
INTO :new.pw_id
FROM DUAL;
END IF;
END;

It appears to be working at least for now. My code appears different than yours. Is it still ok?

Where in the application is this trigger being referenced? I can't seem to find it.

Edited by: nyc2292 on Sep 8, 2008 12:50 PM

That trigger is not defined on your "clients" table so I don't know if this is a different use case or what but the form of the code is fine, even though it differs slightly from my example. If it works, it works.

The trigger is not referenced from within your application. When you define a trigger on a table, the database fires the trigger whenever DML is performed against the table. So it's "automatic" as some would say.

Scott

Your trigger code is fine.

A note about triggers: triggers are database-side code pieces that activate based on conditions in the code itself. In your case, it will fire every time ANYONE (triggers are database-level items not application-level items) inserts a record into encrypt_pw. You could also create a trigger that activates before update, after update, etc. and can be specific even to which column gets updated.

Triggers are commonly used for incrementing unique counters (primary keys) and setting specific values in fields (not the same as defaults). I've also used them to trigger other database actions such as creating records in other tables. Triggers are commonly used for table auditing (but they are not quite as good as no trigger exists for just a SELECT statement). For more information, do a quick search on triggers. They are incredibly powerful and very useful.

Can you include a function from a package within a trigger?

In my above code, I not only need to auto-increment my primary key column, I need to apply a function to another column. The function is to be used with an INSERT and UPDATE statements to the field within that table.

Can I do those two things in the same code? Or, would I have to create another trigger?

Hi,

Yes, you can use functions to set the value on the other field. One important thing you can not do, though, is access any data in the table that's being updated apart from the current record - but as long as you don't need to do that, you should be ok.

To update any field on the current record when it's being created or updated, just reference the field by preceding it with *:NEW.* (eg, :NEW.MYFIELD2). Field values are assigned using the *:=* assignment operator. So, for example:

DECLARE
  ..
  ..
  vNumber NUMBER;
BEGIN
  ..
  SELECT MAX(FIELDZZ) INTO vNumber FROM ANOTHER_TABLE;
  :NEW.FIELDXX := vNumber;
  ...
END;

Andy

Hi,

Thank you for the info.

Only the current record will be udpated.

The user will select a client or insert a new client.

I would like the trigger to do two things: one, auto-increment the primary key (which I'm able to do) and insert/update the record with the function for another field in the same table.

I would like the trigger to do both and not have seperate triggers for each of the two.

I've tried sticking the function within the trigger in various places and have not had much luck.

Hi,

OK- then you should be able to handle it all within the same trigger code.

How this is done exactly would depend on the function (it may help to see any code you've tried so far), but if the function simply receives values and returns a number, for example, you would need to do something like:

CREATE ....
...
DECLARE
...
 vNumber NUMBER;
BEGIN
...
  bit that updates the ID 
...
...
  SELECT MyFunction(value1, value2) INTO vNUMBER FROM DUAL;
  :NEW.NUMBERFIELD := vNUMBER;
...
END;

So, the principle is that you declare a variable, select the function into that variable and then assign the variable to the field on the table.

Andy

I would be quite happy to post some code. Which specific code would you like to see? The package that created the functions? The functions themselves? The trigger?

Also, I have not posted code yet on this forum. How can I post code that will appear neat?

Hi,

I would only need enough to be able to detail the trigger that you should use. So, the trigger itself, how you call the function and with what values or fieldnames and the type of data that the function returns.

When posting code, use the following format:

[code]
Your code
[/code]

Andy

Hi,

Thank you for taking the time to help me out.

This is the trigger I created. It works perfectly for incrementing and inserting my primary key. What I also need the trigger to do is take the password that is typed in and encrypt it.

create or replace TRIGGER encrypt_pw_t1
BEFORE INSERT on encrypt_pw
FOR EACH ROW
BEGIN
IF :new.pw_id IS NULL
THEN SELECT pw_id2_seq.nextval
INTO :new.pw_id
FROM DUAL;
END IF;
END;

These are the two tables that are being used with the package and functions:

CREATE TABLE ENCRYPT_PW 
( 
PW_ID INTEGER CONSTRAINT PW_ID2_PK PRIMARY KEY,
CLIENTID VARCHAR2(50 BYTE),
CLIENT_CODE VARCHAR2(50 BYTE),
PASSWORD VARCHAR2(256 BYTE),
CLIENT_NAME VARCHAR2(256 BYTE)
);


CREATE TABLE ED_ENCRYPT 
( 
PK VARCHAR2(128 BYTE), 
EK RAW(128) 
); 

This is the package and functions being created:

CREATE OR REPLACE PACKAGE ENC_CLIENT 
AS 
FUNCTION ENC_PASSWORD( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2; 

FUNCTION DEC_PASSWORD( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2; 
END; 
/ 

CREATE OR REPLACE PACKAGE BODY ENC_CLIENT 
AS 
g_master_key VARCHAR2(32) := 'ThisIsTheSuperSe'; 
g_unlock_code VARCHAR2(10) := 'OpenSesame'; 
l_mod number := dbms_crypto.ENCRYPT_AES128 
+ dbms_crypto.CHAIN_CBC 
+ dbms_crypto.PAD_PKCS5; 

FUNCTION enc_password ( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2 
AS 
l_key RAW(256); 
l_stored_key RAW(256); 
BEGIN 
IF (p_unlock_code IS NULL 
OR p_unlock_code != g_unlock_code) 
THEN 
RETURN NULL; 
END IF; 

-- Random key generation 
l_key := dbms_crypto.randombytes(16); 

-- Encrypts random key with master key into a stored key 
l_stored_key := dbms_crypto.encrypt(l_key, 
l_mod, 
UTL_I18N.STRING_TO_RAW(g_master_key, 'AL32UTF8') 
); 

-- store unique value and stored key in table 
BEGIN
DELETE ED_ENCRYPT WHERE PK = p_clientid;
END;

INSERT INTO ED_ENCRYPT 
VALUES (p_clientid, l_stored_key); 

-- returns encrypted salary that is encrypted with the random key 
RETURN UTL_ENCODE.BASE64_ENCODE(dbms_crypto.encrypt(UTL_I18N.STRING_TO_RAW(p_password,'AL32UTF8'), 
l_mod, 
l_key)); 
END; 

FUNCTION dec_password ( 
p_password in VARCHAR2,
p_clientid in VARCHAR2,
p_unlock_code in VARCHAR2 DEFAULT NULL) 
RETURN VARCHAR2 
AS 
l_key RAW(256); 
BEGIN 
IF (p_unlock_code IS NULL 
OR p_unlock_code != g_unlock_code) 
THEN 
RETURN NULL; 
END IF; 

SELECT dbms_crypto.decrypt(ek, 
l_mod, 
UTL_I18N.STRING_TO_RAW(g_master_key,'AL32UTF8')) 
INTO l_key 
FROM ED_ENCRYPT 
WHERE pk = p_clientid; 
RETURN UTL_I18N.RAW_TO_CHAR(dbms_crypto.decrypt(UTL_ENCODE.BASE64_DECODE(p_password),l_mod,l_key),'AL32UTF8'); 
END; 
END; 
/

If there is anything else that is needed, please let me know.

Thanks again for your help.

Hi Andy,

Thank you for the help. I actually was not that far off. Instead of referencing :NEW_PASSWORD, I was referencing password.

I will test this out and see how it goes.

Thanks a bunch. You were very helpful.

Hi Andy,

I just wanted to give you an update. Your fix worked. As I suspected, the error of my way was using the actual field names from the table and not the :NEW references.

Thank you and Thank you again. You were a great help!

You're welcome!