Trigger calling procedure

Database

Trigger calling procedure

Hi Experts,

I have a table BOOKMARK, which is getting populated from Java web application.
Whenever an INSER or UPDATE happens to this table, a row level trigger has been invoked and it calls a database procedure.
The issue is related to performance.
When Web application inserts/update records, it waits for the trigger and intern the procedure to get completed.
Is there a way where the trigger executes in background as a separate session in database?
Then java will finish execution soon after the INSERT/UPDATE completes.

Please give your valuable suggestions

Thanks
Ravi..
2 REPLIES

Re: Trigger calling procedure

By definition a trigger executes within the transaction that causes it to be invoked.

To shift the workload to another session, consider using a queue table. The trigger would just insert a row into the queue table. The columns of the queue table would be parameters to be passed to your stored procedure. Then, in another session, some program could continually execute SELECT TOP 1 AND CONSUME from the queue table. This program would then call the stored procedure.

Re: Trigger calling procedure

Hi There,

when i am trying to call a stored proc through triggers i am getting an error message :

ERROR [42000] [Teradata][ODBC Teradata Driver][Teradata Database] A triggered action statement is the wrong kind.

0 records returned.

CREATE TRIGGER Command Failed.

REPLACE TRIGGER AMPSMIS_DEV.UM_FX_SRVC_START_DT_OVRLP_TRG

BEFORE INSERT  ON AMPSMIS_DEV.UM_FX_SRVC_DAY_EXCPT_U ORDER 3

REFERENCING NEW AS NEWROW

FOR EACH ROW

 WHEN  

 ((NEWROW.SWB_PRSN_BETA_ID IS NOT NULL) AND (NEWROW.EXCPT_START_DT IS NOT NULL))

   ( 

     CALL AMPSMIS_DEV.FX_SRVC_EXCPT_START_DT_P(NEWROW.SWB_PRSN_BETA_ID,NEWROW.EXCPT_START_DT,MSG);

   );

Appreciate if somebody can help me in solving this .

Thanks in advance!