OraFAQ Forum: Forms » Dear i want that when a user change or delete any record or row in forms data automatically move to

Home » Developer & Programmer » Forms » Dear i want that when a user change or delete any record or row in forms data automatically move to (forms 6i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Dear i want that when a user change or delete any record or row in forms data automatically move to [message #536991]

Sun, 25 December 2011 13:51

qanita786
Messages: 229
Registered: May 2007
Location: PAKISTAN

Senior Member

Dear i want that when a user change or delete any record or row in forms data automatically move to other table because i want to compare old and new record pls tell me how to do

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537004 is a reply to message #536991]

Mon, 26 December 2011 00:09

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Dear,

Use Database Level trigger on the base of any DML in Forms.

Regards,
Irfan

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537013 is a reply to message #536991]

Mon, 26 December 2011 00:35

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

ok i give u tigger example at database level i did

When particular one user delete rows its rows automatically insert into another table

Here we have two table

EMP and EMP1
Here its if user drop rows from emp table its rows automatically insert into emp1 table

SQL>descemp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ENO                                                VARCHAR2(30)
 ENAME                                              VARCHAR2(30)

SQL>desc emp1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 ENO                                                VARCHAR2(30)
 ENAME                                              VARCHAR2(30)

SQL>

So create a trigger like this

SQL>  create or replace trigger trig_1 BEFORE  DELETE on emp
  2   for EACH ROW
  3   BEGIN
  4   iF DELETING THEN
  5   INSERT INTO EMP1(ENO,ENAME)
  6   VALUES(:OLD.ENO,
  7   :OLD.ENAME);
  8   END IF;
  9   END;
10  /

Trigger created.

Now lets delete rows from emp table

SQL> DELETE FROM EMP WHERE ENO=7777;

The row is delte from emp table but its automatically insert into emp1 table
You can find rows into emp1 table

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537015 is a reply to message #537004]

Mon, 26 December 2011 00:40

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

this tigger also help full you try this also

we create tigger on scott.emp table so before use this tigger you create another new table name emp1 also and after that use this tigger

createorreplacetrigger trig_1 BEFOREDELETEORINSERTORUPDATEonemp
forEACHROW
BEGIN
IF DELETING THEN
INSERTINTOEMP1(ENO,ENAME)
VALUES(:OLD.ENO,:OLD.ENAME);
INSERTINTOEMP1(ENO,ENAME)
VALUES(:NEW.ENO,:NEW.ENAME);
ELSIF INSERTING THEN
INSERTINTOEMP1(ENO,ENAME)
VALUES(:OLD.ENO,:OLD.ENAME);
INSERTINTOEMP1(ENO,ENAME)
VALUES(:NEW.ENO,:NEW.ENAME);
ELSIF UPDATING THEN
INSERTINTOEMP1(ENO,ENAME)
VALUES(:OLD.ENO,:OLD.ENAME);
INSERTINTOEMP1(ENO,ENAME)
VALUES(:NEW.ENO,:NEW.ENAME);
ELSE
DBMS_OUTPUT.PUT_LINE('HI');
ENDIF;
END;
/

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537021 is a reply to message #537015]

Mon, 26 December 2011 01:42

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Quote:

IF INSERTING THEN

INSERTINTOEMP1(ENO,ENAME)
VALUES(:OLD.ENO,:OLD.ENAME);

INSERTINTOEMP1(ENO,ENAME)
VALUES(:NEW.ENO,:NEW.ENAME);
END IF;

Do you have OLD values at the time of Insertion.?

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537028 is a reply to message #537021]

Mon, 26 December 2011 04:15

cookiemonster
Messages: 13926
Registered: September 2008
Location: Rainy Manchester

Senior Member

Or new values at time of deletion?

And do you really write code with no indentation? That's ugly and hard to read.

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537029 is a reply to message #537021]

Mon, 26 December 2011 04:22

x-oracle
Messages: 380
Registered: April 2011
Location: gujarat

Senior Member

i did this

SQL> SELECT * FROM EMP;

ENO                            ENAME
------------------------------ ------------------------------
7788                           SCOTT

SQL> SELECT * FROM EMP1;

ENO                            ENAME
------------------------------ ------------------------------

7788                           SCOTT
7688                           TIGER
7777                           ADMIN

SQL> INSERT INTO EMP VALUES(8888,'DAKLESH');

1 row created.

SQL> SELECT * FROM EMP1;

ENO                            ENAME
------------------------------ ------------------------------

8888                           DAKLESH

7788                           SCOTT
7688                           TIGER
7777                           ADMIN

6 rows selected.

SQL> UPDATE EMP SET ENO=9999 WHERE ENO=8888;

1 row updated.

S

QL> SELECT * FROM EMP1;

ENO                            ENAME
------------------------------ ------------------------------

8888                           DAKLESH
8888                           DAKLESH
9999                           DAKLESH

7788                           SCOTT
7688                           TIGER
7777                           ADMIN

8 rows selected.

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537031 is a reply to message #537029]

Mon, 26 December 2011 04:26

cookiemonster
Messages: 13926
Registered: September 2008
Location: Rainy Manchester

Senior Member

And you've got rows with all nulls. Which really isn't helpful.
Do you want to amend your trigger code so that it doesn't do that?

Report message to a moderator

Re: Dear i want that when a user change or delete any record or row in forms data automatically move [message #537033 is a reply to message #537031]

Mon, 26 December 2011 04:35

ranamirfan
Messages: 535
Registered: January 2006
Location: Pakistan / Saudi Arabia

Senior Member

Dear,


For the row that the trigger is processing:

•For an INSERT trigger, OLD contains no values, and NEW contains the new values.

•For an UPDATE trigger, OLD contains the old values, and NEW contains the new values.

•For a DELETE trigger, OLD contains the old values, and NEW contains no values.

Please read this it'll more help you for creating database trigger.

http://oracle.su/docs/11g/appdev.112/e10472/triggers.htm

Regards,
Irfan

Report message to a moderator

Previous Topic:	Post_Query causes low speed in retrieve data
Next Topic:	possible to create form dynamically

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jul 26 00:22:51 CDT 2024