Re: Simple Create Trigger Problem

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:oGuvd.566743$D%.422686_at_attbi_s51...
|
| <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);
| END IF;
|
| END;
| /
|
| Also you can't commit in a trigger and shouldn't.
| Jim
|
|
|

Arijit,

Jim is correct about execute immediate and commits in triggers. Execute immediate is ONLY necessary if the SQL statement is dynamic in the structure of the statement or the objects it references -- but not when only the data values are dynamic.

Specific to your error, if you did need to use execute immediate (and if you ever think you do, make sure you study the syntax first, and use bind variables whenever possible) you will need to be careful about referencing literal values in the statement you build.

In this case, because you used execute immediate without bind variables and just concatenated the :NEW variables to your generated statement, the value in :NEW.NAME was included in your INSERT statement without single-quotes, so oracle interpreted it as a column, hence the error at line 4 in your trigger. If execute immediate was warranted, and you chose to concatenate a string literal, you must also include single quotes in the concatenated statement. But Jim's approach is best practice in this situation.

Again, do not get in the habit of using execute immediate unless you actually are generating dynamically structured sql statements (review the PL/SQL manual on database access for more information). When there is a legitimate reason to use execute immediate, always build the dynamic statement and assign it to a variable, then pass the variable to execute immediate. That way, if you have an error, you can easily debug print the variable and examine the syntax of the generated statement.

++ mcs Received on Tue Dec 14 2004 - 06:50:50 CST