Moving a record to another table in a trigger, Old topic was closed.. reposting Options

[dbuch]

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

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!

[dbuch]

My bad.. all working now!!

thanks for your help in this, helped me heaps.

Cheers

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