Passing parameter to a trigget

[tiruma...@gmail.com]

Hai,

I have a trigger in Postgrresql DB, i want to pass  a parameter to the trigger so that that trigget will execute by considering the parameter.. I am using JOOQ,Spring.. Can u share any basic example to call trigger or passing parameter to trigger in JOOQ..

Thanking you,

Tirumala.

[Lukas Eder]

Hello Tirumala,

Thanks for your enquiry. Usually, you don't pass a "parameter" to a trigger but the trigger will pick out the relevant changes from an INSERT / UPDATE / DELETE statement that it cares about. Could you perhaps share a bit more information about the problem that you're trying to solve? Also, would you mind posting the trigger here on the user group to help us understand what you're trying to do?

Cheers,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[tiruma...@gmail.com]

Hai Lukas,

Thank you so much for your reply. 

CREATE OR REPLACE FUNCTION cwc_documnetsequence_trg()

  RETURNS trigger AS

$BODY$ DECLARE 

  v_seq_id VARCHAR(32) ; 

  v_incrementno integer ; 

  v_currentnext integer ; 

  

  BEGIN

select docnosequence_id into v_seq_id from cw_doctype where cw_doctype_id = '1';

select sum(incrementno+currentnext) into v_currentnext from cw_sequence where cw_sequence_id = v_seq_id;

update cw_sequence set currentnext = v_currentnext where cw_sequence_id = v_seq_id;

return NEW;

  END;

  $BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

ALTER FUNCTION cwc_documnetsequence_trg()

  OWNER TO postgres;

the above query is to create a function. which i am calling from a trigger.

CREATE TRIGGER documnetsequence

  AFTER INSERT

  ON cwc_lead

  FOR EACH ROW

  EXECUTE PROCEDURE cwc_documnetsequence_trg();

in that procedure i need dynamic cw_doctype_id value from java.. how can i achieve the above senorio.. is there any other way?..  

Awaiting for ur response

[Lukas Eder]

Hmm, why aren't you using PostgreSQL's built-in sequences? Your solution seems to be rather risky, in terms of potential race conditions on the cw_sequence table...

Anyway, the only way to access INSERT statement context that I can think of, is by accessing columns of the CWC_LEAD table via the NEW prefix. E.g. in the trigger function, you can perhaps access NEW.CW_DOCTYPE_ID from the INSERT statement, or some other value that helps you identify that CW_DOCTYPE_ID value.

Does that help?

--

[tiruma...@gmail.com]

We have another problem by creating normal sequence which actually full fills my requirement. So as per your reply i can get   NEW.CW_DOCTYPE_ID in the function right??

if it is, My actual insertion is happening from java.. can i get  recent CW_DOCTYPE_ID from  NEW.CW_DOCTYPE_ID??

[Lukas Eder]

Hi Tirumala,

I don't know if you can get that value from "NEW". NEW is a variable that corresponds to the record that you were inserting when the trigger fired. Would you mind sharing a bit more information about your current task? E.g. can you show the DDL for all the tables that are involved here?

Cheers,
Lukas

--