Home » SQL & PL/SQL » SQL & PL/SQL » query for deleting the minimum updated record.  () 1 Vote
query for deleting the minimum updated record. [message #249363] Wed, 04 July 2007 03:25 Go to next message
lav_pandey
Messages: 1
Registered: July 2007
Junior Member
Hello Everybody,

I have table USER_RECENT_PROJECTS which has SIX columns USER_NAME,PROJECT_ID,CREATED_BY,CREATED_ON,UPDATED_BY
and UPDATED_ON.The purpose of having this table to get 5 recent PROJECTS
on which user has worked on.
I have trigger called RECENT_PRJ_TRIGG which IS FIRED when the data is inserted or updated on PROJECT table.After this trigger calls procedure PROC_USER_RECENT_PRJ and that procedure puts the data in this table.
It is inserting the data upto 5 records when the six records comes it deleting the record which is least UPDATED_ON from the table USER_RECENT_PROJECTS but the problem is it is deleting
the record from other user that i don't want.I want to delete the the record which is
least UPDATED_ON from particular user.

Please help me on this issue.

Here is the trigger

CREATE TRIGGER RECENT_PRJ_TRIGG
AFTER INSERT OR UPDATE ON PROJECT
FOR EACH ROW
DECLARE
NUMBER_OF_PROJECTS NUMBER:=0;
EXISTING_PROJECT_ID NUMBER:=0;

BEGIN
SELECT COUNT(*) INTO NUMBER_OF_PROJECTS FROM USER_RECENT_PROJECTS WHERE USER_NAME=:NEW.UPDATED_BY;
SELECT PROJECT_ID INTO EXISTING_PROJECT_ID FROM USER_RECENT_PROJECTS WHERE PROJECT_ID=:NEW.PROJECT_ID AND USER_NAME=:NEW.UPDATED_BY;
NVLX.PROC_USER_RECENT_PRJ(NUMBER_OF_PROJECTS,:NEW.PROJECT_ID,EXISTING_PROJECT_ID,:NEW.UPDATED_BY,:NEW.CREATED_BY,:NEW.CREATED_ON);
EXCEPTION
WHEN NO_DATA_FOUND THEN
NVLX.PROC_USER_RECENT_PRJ(NUMBER_OF_PROJECTS,:NEW.PROJECT_ID,0,:NEW.UPDATED_BY,:NEW.CREATED_BY,:NEW.CREATED_ON);
END;


And this is the procedure which is inserting the data

CREATE OR REPLACE PROCEDURE PROC_USER_RECENT_PRJ (
NUMBER_OF_PROJECTS IN NUMBER,
NEW_PROJECT_ID IN PROJECT.PROJECT_ID%TYPE,
EXISTING_PROJECT_ID IN USER_RECENT_PROJECTS.PROJECT_ID%TYPE,
USER_NAME IN CONTENT_USER.USER_NAME%TYPE,
CREATED_BY IN PROJECT.CREATED_BY%TYPE,
CREATED_ON IN PROJECT.CREATED_ON%TYPE)
IS
MAX_RECENT_PROJECTS NUMBER := 5;

BEGIN

IF NUMBER_OF_PROJECTS<=MAX_RECENT_PROJECTS AND EXISTING_PROJECT_ID=NEW_PROJECT_ID THEN
UPDATE USER_RECENT_PROJECTS SET USER_RECENT_PROJECTS.UPDATED_ON=SYSDATE,USER_RECENT_PROJECTS.UPDATED_BY=USER_NAME WHERE PROJECT_ID=EXISTING_PROJECT_ID
AND USER_RECENT_PROJECTS.USER_NAME=USER_NAME;

ELSE IF NUMBER_OF_PROJECTS<MAX_RECENT_PROJECTS AND EXISTING_PROJECT_ID!= NEW_PROJECT_ID THEN
INSERT INTO USER_RECENT_PROJECTS VALUES (USER_NAME,NEW_PROJECT_ID,CREATED_BY,CREATED_ON,USER_NAME,SYSDATE);

ELSE IF NUMBER_OF_PROJECTS=MAX_RECENT_PROJECTS AND EXISTING_PROJECT_ID!= NEW_PROJECT_ID THEN
DELETE FROM USER_RECENT_PROJECTS WHERE USER_RECENT_PROJECTS.PROJECT_ID IN(
SELECT PROJECT_ID FROM USER_RECENT_PROJECTS
WHERE UPDATED_ON=(SELECT MIN(UPDATED_ON) FROM USER_RECENT_PROJECTS
WHERE USER_RECENT_PROJECTS.USER_NAME=USER_NAME));

INSERT INTO USER_RECENT_PROJECTS VALUES (USER_NAME,NEW_PROJECT_ID,CREATED_BY,CREATED_ON,USER_NAME,SYSDATE);
END IF;
END IF;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NVLX.PROC_USER_RECENT_PRJ(NUMBER_OF_PROJECTS,NEW_PROJECT_ID,0,USER_NAME,CREATED_BY,CREATED_ON);
END PROC_USER_RECENT_PRJ;

Please help me on this issue.
Thanks in advance.....>
Re: query for deleting the minimum updated record. [message #249386 is a reply to message #249363] Wed, 04 July 2007 04:22 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is useless to report the message to get a faster answer.
We don't owe you any answer.
Please patient.
In the meantime:
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Break your lines to max 80-100 characters when you format.
Please always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: pls help
Next Topic: Need help in a query.
Goto Forum:
  


Current Time: Wed Dec 07 22:06:59 CST 2016

Total time taken to generate the page: 0.09737 seconds