Re: DUPPICATE DATA FIELD IN INSERT AND UPDATE TRIGGER

On 19 Jul 2002 06:13:17 -0700, pcouas_at_infodev.fr wrote:

>Hi,
>
>I tried to dupplicate datafield from REFERENCE to NREFERENCE in oracle
>8.1.6.
>i haven't any error message but data is not dupplicated in my new
>field.
>Why
>
>Thanks
>
>Philippe
>PS
>i need to dupplicate data field because i need to have complex index
>with dupplicate data.
>
>Thanks
>Philippe
>
>
>
>CREATE TABLE MATABLE (
>REFERENCE VARCHAR2(15),
>NREFERENCE VARCHAR2(15));
>commit
>
>create or replace procedure UpdateMaTable ( b1 varchar2 ,p_rowid
>ROWID)
>is pragma autonomous_transaction;
>begin
>update matable set nreference=b1 where ROWID=p_rowid;
>commit;
>EXCEPTION
>when OTHERS THEN ROLLBACK;
>end UpdateMaTable;
>
>CREATE OR REPLACE TRIGGER Reference_matable AFTER INSERT OR UPDATE ON
>MATABLE
>FOR EACH ROW
>DECLARE
>v_current_rowid ROWID;
>BEGIN
>if :new.REFERENCE <> :new.NREFERENCE or :new.NREFERENCE is NULL then
>UpdateMaTable ( :new.REFERENCE, v_current_rowid);
>end if;
>end;
>/
>
>
>SELECT * FROM MATABLE;
>
>
>INSERT INTO MATABLE (REFERENCE) VALUES ('OOOOOOOOOOO');
>
>Data is not in two fields ???

Just use

CREATE OR REPLACE TRIGGER Reference_matable BEFORE INSERT OR UPDATE ON
MATABLE
FOR EACH ROW
BEGIN
if :new.REFERENCE <> :new.NREFERENCE or :new.NREFERENCE is NULL then  :new.NREFERENCE := :new.REFERENCE;
end if;
end;
/

and forget about the rest. You CAN'T update the table you are already updating.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Fri Jul 19 2002 - 12:57:34 CDT