Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Moving a record to another table in a trigger, Old topic was closed.. reposting
dbuch
post Mar 12 2012, 02:14 AM
Post #1


Newbie
*

Group: Members
Posts: 5
Joined: 23-November 11
Member No.: 46,310



I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..

Can you tell me what' s wrong with this? :

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;

PROCEDURE tab1_statement_change IS
l_count NUMBER(10);
BEGIN
FOR i IN (SELECT * FROM tempjob) LOOP
SELECT COUNT(*)
INTO l_count
FROM tab1;

delete from passations where numass = i.numass and datcre = i.datcre;
END LOOP;
DELETE FROM tempjob;
END tab1_statement_change;

END trigger_api;


If I could at least compile, I could try and get to test if it works for me...

thanks

cheers




QUOTE (burleson @ Mar 10 2012, 12:33 AM) *
Hi David,

>> Doing this process from code is not an option and MUST happen automatically via triggers.

The mutating trigger error can sometimes be avoided:

Please read:

http://www.dba-oracle.com/t_avoiding_mutat...table_error.htm

Good Luck!


Go to the top of the page
 
+Quote Post
dbuch
post Mar 12 2012, 02:49 AM
Post #2


Newbie
*

Group: Members
Posts: 5
Joined: 23-November 11
Member No.: 46,310



My bad.. all working now!!

thanks for your help in this, helped me heaps.

Cheers

QUOTE (dbuch @ Mar 12 2012, 08:14 AM) *
I have looked at the code you pointed me to, and have attempted to get it to work using a package, but I cant even get the package to compile..

Can you tell me what' s wrong with this? :

CREATE OR REPLACE PACKAGE BODY trigger_api AS

PROCEDURE tab1_row_change (p_numass IN varchar2,
p_datcre IN date) IS
BEGIN
INSERT INTO tempjob (numass, datecre) VALUES (p_numass, p_datcre);
END tab1_row_change;

PROCEDURE tab1_statement_change IS
l_count NUMBER(10);
BEGIN
FOR i IN (SELECT * FROM tempjob) LOOP
SELECT COUNT(*)
INTO l_count
FROM tab1;

delete from passations where numass = i.numass and datcre = i.datcre;
END LOOP;
DELETE FROM tempjob;
END tab1_statement_change;

END trigger_api;


If I could at least compile, I could try and get to test if it works for me...

thanks

cheers

Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th October 2014 - 10:27 PM