| Sequences, Triggers, currval and Transactions [message #373563] |
Mon, 23 April 2001 14:25  |
Ken Miller
Messages: 4
Registered: April 2001
|
Junior Member |
|
|
Hi there,
We're using a sequence + insert trigger to implement an identity column. The problem we're having is that this table has a BLOB column, so in order to complete an insert, we have to turn right around and select the row we just inserted to get the BLOB locator. None of the other data in the table can be used to reliably identify the row. So my question is:
Can we do this safely:
INSERT INTO mytable (col1, col2) VALUES (1,2);
/* trigger automatically inserts unique id */
SELECT blobcol FROM mytable WHERE id = myseq.currval;
COMMIT;
Or should we abandon the trigger, get the sequence value manually?
|
|
|
|
|
|
|
|
| Re: Sequences, Triggers, currval and Transactions [message #373566 is a reply to message #373565] |
Mon, 23 April 2001 15:07  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Is there a reason why won't work? It's easy to get the sequence number - proc/funct can pass it back to the app.
declare
v_id number;
begin
select my_seq.nextval into v_id from dual;
insert into test_1 values (v_id, 'hello1');
dbms_output.put_line('id='||v_id);
end;
/
PROCEDURE Put_rec (i_msg in nocopy VARCHAR, o_id out number )
IS
BEGIN
select my_seq.nextval into o_id from dual;
INSERT INTO test_1 VALUES (o_id, i_msg);
END;
/
|
|
|
|
| Re: Sequences, Triggers, currval and Transactions [message #373569 is a reply to message #373565] |
Mon, 23 April 2001 19:12 |
Ken Miller
Messages: 4
Registered: April 2001
|
Junior Member |
|
|
Yes, that would work. But currently we're getting nextval within the trigger, so it's not necessary to use it on the insert. Even if we do it that way we're still left with the problem of finding the value that was inserted last time, within the same session. It seems like currval might work that way, but it's not clear.
Ken
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
