CODEWITHSUNDEEP
plsqltriggersoracle-apex
Spags
Spags

Reputation: 541

Oracle APEX - If statement in database trigger

Ok. So I know how to create a trigger that makes changes to Table B when an action is made on Table A. that is reletively easy and I am comfortable doing so. What I have been unable to do is to create a trigger that contains an if statement so that it executes if certain criteria are met.

Example:

Table A & Table B are related. Table A contains data concerning documents that are currently in processing which are identified by a unique processing code. Table B contains data about finalized and released documents which are identified by a Publication ID. Table A and Table B are related because a document may undergo many revisions and therefore it may appear multiple times in Table A (unique processing code but same Publication ID) while still only appearing in Table B once. The Publication ID remains the same throughout the course of the document's life no matter how many revisions it undergoes.

Table A is filled via an input form where people submit requests concerning documents they would like to have us process. They have the option of selecting to process a new document or if they would like to revise a current document that is already in Table B. This is done via a radio group (New, Revision etc...). If 'Revision' is selected then a select list box, that is populated with Publication IDs from Table B, appears and the user can select which document to edit.

What I need to do is create a trigger, or some other means, that will, when a request is submitted via our input form, check to see if it is for a revision of a document in Table B and mark the 'Status' column in Table B to 'Revision in Progress'.

So it would need to be either conditionally called or have some kind of if statement in the trigger itself. The condition, again, would be if 'Revision' is selected on Table A's input form then set the Status of the selected Publication ID to 'Revision in Progress'.

Thanks!

Upvotes: 1

Views: 687

Answers (2)

Sanjeev Sapre
Sanjeev Sapre

Reputation: 36

It should be normal row level trigger on table A,

A pseudo code should be something like this..

create or replace trigger on table A for each row ..... if :NEW.process_selected = 'Revision' then update b set status = 'Revision in Progress' where publication_id = :publication_id ; end if; end ;

-Sanjeev

Upvotes: 2

Gudmundur Orn
Gudmundur Orn

Reputation: 2003

It's been a long time since I used APEX, but can't the form submission be to a pl/sql procedure rather than directly to a table? Then write a procedure that takes the appropriate action depending on the input parameters.

Upvotes: 1

Related Questions