Return id value from trigger 1

[TelcoBT]

My apologies if this question has already been asked elsewhere, my browser keeps timing out on searches today, so I can't check.

I'm using ASP VBScript to call a stored procedure, this adds a data record. On insert, a trigger generates an 'id' for the inserted record, what I need to know is the value of the id, and return it to the calling script.

The stored procedure code is shown below, does anybody know if there is a simple/accurate method of getting back the generated id? I could try a select MAX on the id, but cannot guarantee that another user has not added a record in between, I wondered if the trigger value generated is accessible from this stored proc and so could be returned in the o_message_id parameter.

TIA

John


PROCEDURE AddMessage(
i_message IN BROADCAST_MESSAGE.MESSAGE%TYPE,
i_date_from IN BROADCAST_MESSAGE.DATE_FROM%TYPE,
i_date_to IN BROADCAST_MESSAGE.DATE_TO%TYPE,
o_message_id OUT BROADCAST_MESSAGE.MESSAGE_ID%TYPE,
o_debug OUT VARCHAR2
)
IS

v_message_id NUMBER;
v_strsql VARCHAR2(2000);

BEGIN

v_strsql:='INSERT INTO BROADCAST_MESSAGE (MESSAGE,DATE_FROM,DATE_TO) ';

v_strsql:=v_strsql || 'VALUES ('|| i_message||','||i_date_from||','||i_date_to||')';

--o_debug:=o_debug||'<br>'||v_strsql;

execute immediate v_strsql;

COMMIT;

-- here would like to get back the message_id so I could get it back to the calling script.


END AddMessage;

 

[jee]

Since you are using Dynamic Sql, the best way would be to use the Returning clause in your insert statement

For instance, replace the EXECUTE statement with this:

v_strsql := v_strsql || ' RETURNING pk_name into :1'
EXECUTE IMMEDIATE v_strsql RETURNING INTO v_id;

 

[TelcoBT]

Good Afternoon (here in the UK) Jee,

Many thanks for your post, pointed me in the right direction, although I couldn't get the 'dynamic' sql to work, so I re-wrote the code, shown below, and it works fine

Thanks again!

John

PROCEDURE AddMessage(
i_message IN BROADCAST_MESSAGE.MESSAGE%TYPE,
i_date_from IN BROADCAST_MESSAGE.DATE_FROM%TYPE,
i_date_to IN BROADCAST_MESSAGE.DATE_TO%TYPE,
o_message_id OUT BROADCAST_MESSAGE.MESSAGE_ID%TYPE,
o_debug OUT VARCHAR2
)
IS

v_message_id NUMBER;
v_strsql VARCHAR2(3000);

BEGIN

INSERT INTO BROADCAST_MESSAGE
VALUES (SEQ_BROADCAST_MESSAGE.nextval , i_message, i_date_from ,i_date_to)
RETURNING MESSAGE_ID into o_message_id;

COMMIT;

END AddMessage;