Re: Simple Create Trigger Problem

<arijitchatterjee123_at_yahoo.co.in> wrote in message news:1103000135.516627.56760_at_f14g2000cwb.googlegroups.com...
> Dear Faculties,
> Once again I am disturbing you.I am explaining the scenario.
> I am two tables, tab1 and tab1Copy.
> -----------------------------------
> create table tab1
> (
> name varchar2(10),
> amount number
> );
>
> create table tab1Copy
> (
> name varchar2(10),
> amount number,
> SumAmount number
> );
> -----------------------------------
> I have created a trigger TRG_TAB1 on tab1,Whenever any new entry taking
> place that should insert the same in tab1copy table
> -----------------------------------
> CREATE OR REPLACE TRIGGER TRG_TAB1
> AFTER INSERT OR UPDATE OR DELETE
> ON TAB1 FOR EACH ROW
> BEGIN
> IF INSERTING THEN
> DBMS_OUTPUT.PUT_LINE(:NEW.NAME );
> EXECUTE IMMEDIATE 'INSERT INTO TAB1COPY(NAME,AMOUNT)
> VALUES('||:NEW.NAME ||','|| :NEW.AMOUNT ||')';
> EXECUTE IMMEDIATE 'UPDATE TAB1COPY SET SUMAMOUNT=0 WHERE NAME='||
> :NEW.NAME ;
> COMMIT;
>
> END IF;
>
> END;
> --------------------------------------
> But I am getting the error at the time of insertion.
> --------------------------------------
> SQL> INSERT INTO tab1(NAME,AMOUNT) VALUES('ARIJIT',100);
> INSERT INTO tab1(NAME,AMOUNT) VALUES('ARIJIT',100)
> *
> ERROR at line 1:
> ORA-00984: column not allowed here
> ORA-06512: at "SCOTT.TRG_TAB1", line 4
> ORA-04088: error during execution of trigger 'SCOTT.TRG_TAB1'
> ----------------------------------------
> Please help me out.
> Thanks in advance
> Regards
> Arijit Chatterjee
>

Why are you using execute immediate? There is no need to. Just

CREATE OR REPLACE TRIGGER TRG_TAB1
 AFTER INSERT OR UPDATE OR DELETE
 ON TAB1 FOR EACH ROW
 BEGIN
 IF INSERTING THEN
    INSERT INTO TAB1COPY(NAME,AMOUNT,SUMAMOUNT)

                                         VALUES(:NEW.NAME ,:NEW.AMOUNT ,0);

Also you can't commit in a trigger and shouldn't. Jim Received on Mon Dec 13 2004 - 23:16:04 CST