472,122 Members | 1,518 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,122 software developers and data experts.

"SQL Statement ignored" and "ORA-01733: virtual column not allowed here" errors

Expand|Select|Wrap|Line Numbers
  1. create or replace trigger emp_trigger
  2.    after update or delete on employee 
  3.    for each row
  4. begin
  5.  if updating then
  6.      insert into empupdate(empno,ename,job,mgr,sysdate,sal,comm,deptno) values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  7. elsif deleting then 
  8.      insert into empdelete(empno,ename,job,mgr,sysdate,sal,comm,deptno) values(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  9. end if;
  10. end;
  11. /
[Please use CODE tags when posting source code. Thanks! --pbmods]

error is

-------- -----------------------------------------------------------------
3/6 PL/SQL: SQL Statement ignored
3/48 PL/SQL: ORA-01733: virtual column not allowed here
5/6 PL/SQL: SQL Statement ignored
5/48 PL/SQL: ORA-01733: virtual column not allowed here
Jun 20 '07 #1
2 18701
5,821 Expert 4TB
Changed thread title to better describe the problem (did you know that threads whose titles contain phrases such as 'plz help' actually get FEWER responses?).

Moving to the Oracle forum....
Jun 20 '07 #2
8,127 Expert 4TB
Use this code.
It has been tested.

Expand|Select|Wrap|Line Numbers
  1. CREATE OR REPLACE TRIGGER emp_trigger after UPDATE OR DELETE ON emp FOR each row
  2. begin
  3. IF updating then
  4.      INSERT INTO empupdate(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  5. elsif deleting then
  6.      INSERT INTO empdelete(empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES(:old.empno,:old.ename,:old.job,:old.mgr,:old.hiredate,:old.sal,:old.comm,:old.deptno);
  7. end IF;
  8. end;

1.column name can;t be sysdate as it is a PSEUDO COLUMN
Jun 21 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Robert Bralic | last post: by
reply views Thread by Frederick Noronha \(FN\) | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.