Home » SQL & PL/SQL » SQL & PL/SQL » trigger problem (10g)
trigger problem [message #361888] Fri, 28 November 2008 07:13 Go to next message
rahulsql
Messages: 18
Registered: August 2008
Junior Member
INDV_REG_VER_DETAIL TABLE

INDV_REG_REQ_NUMBER, INDV_VER_TASK_NUMBER, CHECKLIST_POINT_ID, VERIFICATION_RESULT
..................................................................................
2 2 1 P
2 2 2 P
2 2 3 P




INDV_REG_VER_TASK_ASSIGNMENT TABLE

INDV_REG_REQ_NUMBER, INDV_VER_TASK_NUMBER, VERIFICATION_RESULT CHECKLIST_COUNT, CHECKLIST_COMPLETED
......................................................................................................

2 2 P 3 0



WHEN IN INDV_REG_VER_DETAIL TABLE EACH VERIFICATION RESULT IS MADE AS 'A' OR 'R'
then INDV_REG_VER_TASK_ASSIGNMENT TABLE CHECKLIST_COMPLETED should be incremented for taht we write trigger
CREATE OR REPLACE TRIGGER INDCHKLISTCOUNTUPDATE
BEFORE UPDATE
ON INDV_REG_VER_DETAIL
FOR EACH ROW
DECLARE

BEGIN
IF :NEW.verification_result IN ('A','R')
THEN
UPDATE INDV_REG_VER_TASK_ASSIGNMENT
SET CHECKLIST_COMPLETED =CHECKLIST_COMPLETED+1
WHERE INDV_REG_REQ_NUMBER=:NEW.INDV_REG_REQ_NUMBER
AND INDV_VER_TASK_NUMBER=:NEW.INDV_VER_TASK_NUMBER;

END IF;

END;

now i need one trigger such that when CHECKLIST_COUNT equals to CHECKLIST_COMPLETED in INDV_REG_VER_TASK_ASSIGNMENT TABLE

then we have to check results of VERIFICATION_RESULT in INDV_REG_VER_DETAIL TABLE.if all 3 results are R then update
INDV_REG_VER_TASK_ASSIGNMENT table VERIFICATION_RESULT as R .
if all 3 results are A then upadte
INDV_REG_VER_TASK_ASSIGNMENT table VERIFICATION_RESULT as A .
if all 3 results are not matching then upadte
INDV_REG_VER_TASK_ASSIGNMENT table VERIFICATION_RESULT as F .
Re: trigger problem [message #361891 is a reply to message #361888] Fri, 28 November 2008 07:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: trigger problem [message #361950 is a reply to message #361888] Fri, 28 November 2008 12:18 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
If you are intent upon a trigger based solution, then this problem may be more easily handled with instead-of-triggers. Do a GOOGLE for it.

Kevin
Previous Topic: How to use the CASE STATEMENT in PL/SQL
Next Topic: Re: ORA-13774: insufficient privileges to select data from the workload repository
Goto Forum:
  


Current Time: Sun Dec 04 06:56:42 CST 2016

Total time taken to generate the page: 0.11973 seconds