Home » SQL & PL/SQL » SQL & PL/SQL » insead of trigger
insead of trigger [message #219722] Thu, 15 February 2007 12:07 Go to next message
Messages: 11
Registered: February 2007
Location: noida, sec-15
Junior Member
hello eveyone

i have created a trigger "instead of trigger"
but fail to insert data, please tell how will i do ?

SQL> ed
Wrote file afiedt.buf

  1  create or replace trigger tr_instead
  2  instead of insert or update or delete on v_complex
  3  begin
  4   if inserting then
  5    insert into emp(ename, empno, deptno)
  6      values(:new.ename, :new.empno, :new.deptno);
  7    insert into dept(deptno, dname)
  8      values(:new.deptno, :new.dname);
  9   elsif deleting then
 10      delete from emp
 11       where empno=:old.empno;
 12     delete from dept
 13      where deptno=:old.deptno;
 14   elsif updating then
 15     update emp set ename=:new.ename, deptno=:new.deptno
 16       where empno=:old.empno;
 17    end if;
 18* end;
SQL> /

Trigger created.

my trigger is consists of two table emp & dept.

SQL> ed
Wrote file afiedt.buf

  1  create view v_complex as
  2  select d.deptno, e.ename, e.empno, d.dname
  3   from emp e, dept d
  4* where d.deptno=e.deptno
SQL> /

View created.

please tell ne how will i insert data

i m getting error-

SQL> ed
Wrote file afiedt.buf

  1  insert into v_complex(d.deptno, e.ename, e.empno, d.dept)
  2*  values(10, 'sherya', 7668, 'design')
SQL> /
insert into v_complex(d.deptno, e.ename, e.empno, d.dept)
ERROR at line 1:
ORA-00904: invalid column name

Re: insead of trigger [message #219745 is a reply to message #219722] Thu, 15 February 2007 17:06 Go to previous messageGo to next message
Messages: 20850
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
a) get rid of aliases
b) if a view contains (deptno, ename, empno, dname), why are you inserting (deptno, ename, empno, dept)?

In other words, something like this *might* work (depending on possible violation of primary key or referential integrity constraints):
insert into v_complex
  (deptno, ename, empno, dname)
  (10, 'sherya', 7668, 'design')
Re: insead of trigger [message #220234 is a reply to message #219722] Mon, 19 February 2007 13:22 Go to previous message
Bill B
Messages: 1473
Registered: December 2004
Senior Member
You also have a big problem with your trigger. Just because you are deleting a specific emp, it does NOT mean that you should delete the entire department, which will almost concertedly still be in use.
Previous Topic: how can say that truncate is faster than delete......
Next Topic: Handling duplicate values
Goto Forum:

Current Time: Sun Oct 23 09:19:56 CDT 2016

Total time taken to generate the page: 0.12830 seconds