Home » SQL & PL/SQL » SQL & PL/SQL » update based on other table records (oracle 10g,xp)
update based on other table records [message #429498] Wed, 04 November 2009 04:42 Go to next message
sekharsomu
Messages: 69
Registered: December 2008
Member
CREATE TABLE "SCOTT"."SEATALLOTMENT" 
   (	"YEAR" NUMBER(4,0), 
	"COLLEGECODE" CHAR(4 BYTE), 
	"COURSECODE" CHAR(3 BYTE), 
	"REGISTRATIONNO" NUMBER(12,0) , 
	"ALLOCATEDTIME" TIMESTAMP (6) DEFAULT systimestamp );



CREATE TABLE "SCOTT"."COURSESEATS" 
   (	"YEAR" NUMBER(4,0), 
	"COLLEGECODE" CHAR(4 BYTE), 
	"COURSECODE" CHAR(3 BYTE), 
	"CATEGORYCODE" CHAR(2 BYTE), 
	"MAXINTAKE" NUMBER(4,0), 
	"AVAILABLE" NUMBER(4,0)
   ) 


insert statements:
Insert into COURSESEATS (YEAR,COLLEGECODE,COURSECODE,CATEGORYCODE,MAXINTAKE,AVAILABLE) 
values (2009,'krcl','CSE','OC',2009,60);
Insert into COURSESEATS (YEAR,COLLEGECODE,COURSECODE,CATEGORYCODE,MAXINTAKE,AVAILABLE) 
values (2009,'krcl','EEE','OC',2009,60);
Insert into COURSESEATS (YEAR,COLLEGECODE,COURSECODE,CATEGORYCODE,MAXINTAKE,AVAILABLE) 
values (2009,'krcl','ECE','OC',2009,60);


Insert into SEATALLOTMENT (YEAR,COLLEGECODE,COURSECODE,REGISTRATIONNO,ALLOCATEDTIME) 
values (2009,'krcl','CSE',101102103104,
to_timestamp('03-NOV-09 12.36.54.500000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


PROBLEM :

Now i want to UPDATE reducing the AVAILABLE column by 1 in COURSESEATS table based on common columns collegecode,coursecode for a ROW inserted into SEATALLOTMENT table ,i am confused to what approach i have to follow whether its a procedure or a trigger
CASE:
Here in this case as i insert a row with krcl,cse as college code and course code respectively into seatallotment table the available column in courseseat table for the respective row with mentioned common column must become 59 from 60



[Updated on: Wed, 04 November 2009 04:49] by Moderator

Report message to a moderator

Re: update based on other table records [message #453825 is a reply to message #429498] Sun, 02 May 2010 00:57 Go to previous messageGo to next message
sibanjan
Messages: 3
Registered: May 2010
Location: pune,india
Junior Member
You can write a trigger upon the seatallotment table and update the available courseseat table


CREATE OR REPLACE TRIGGER trig_col_seats
BEFORE insert on seatallotment
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
update courseseats set available=available-1
where collegecode=:NEW.collegecode
and coursecode=:NEW.coursecode;
commit;
END;
Re: update based on other table records [message #453829 is a reply to message #453825] Sun, 02 May 2010 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why an autonomous transaction?
What happen if the main transaction rolls back?

NEVER uses autonomous transaction unless you perfectly knows what you do and what are the implications. Autonomous transaction have very few use cases, logging is an example.

Regards
Michel

Re: update based on other table records [message #453831 is a reply to message #429498] Sun, 02 May 2010 01:38 Go to previous messageGo to next message
sibanjan
Messages: 3
Registered: May 2010
Location: pune,india
Junior Member
thanx michel
Actually without thinking any implication , i wrote it.
Revised code is

CREATE OR REPLACE TRIGGER trig_col_seats
BEFORE insert on seatallotment
FOR EACH ROW

BEGIN
update courseseats set available=available-1
where collegecode=:NEW.collegecode
and coursecode=:NEW.coursecode;

END;
/
Re: update based on other table records [message #453832 is a reply to message #453831] Sun, 02 May 2010 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64106
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know if your code is right as I didn't understand OP's requirement.
For your next posts, 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, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel
Re: update based on other table records [message #453877 is a reply to message #429498] Sun, 02 May 2010 16:26 Go to previous message
sekharsomu
Messages: 69
Registered: December 2008
Member
What a great community. This why i love being here i have asked this query almost 6 months back and i still see people answering it to perfection. kudos! for that and Micheal, sibanjan second query suites my requirement and once again thanks for your interest Mike i actually finished the task with a similar query
Thanks everyone.
Previous Topic: Roles
Next Topic: Report
Goto Forum:
  


Current Time: Mon Dec 05 02:50:26 CST 2016

Total time taken to generate the page: 0.21187 seconds