Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    9

    Unanswered: URGENT:trigger on insert statement

    hi firend

    i have one table Emp (EID, ENAME, DEPT_NAME, HIREDATE, SAL , ETC...)
    now according to business logic at the time of insert statement a trigger fires and as per dept it generate the EID and insert it into table
    example:

    if DEPT = sales then EID = S01, S01,S03,.....etc
    if DEPT = admin then EID = A01, A02, A03, ....etc


    now can anyone please assist me to design this trigger

    its very urgent i need to submit this query to my TL today it self
    please assist me on urgent basis.

    many many thanks in advance.

    regards,
    Shanu
    Last edited by contact2amitesh; 07-27-11 at 15:52.

  2. #2
    Join Date
    Jul 2011
    Posts
    9
    hi friends ,

    i tried this with no lock
    -------------------------------


    create or replace trigger EMP_Insert_trig
    after insert ON emp
    for each row
    declare
    pragma autonomous_transaction;
    begin
    insert into emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) values(:new.empno,:new.ename,:new.job,:new.mgr,:ne w.hiredate,:new.sal,:new.comm,:new.deptno);
    end;
    /

    SQL> Trigger Created ...



    but when we try to insert any value into emp, it reflect me following error
    -----------------------------


    SQL> insert into emp values(234,'aa','aa',123,sysdate,12,10,20)

    insert into emp values(234,'aa','aa',123,sysdate,12,10,20)
    *
    ERROR at line 1:
    ORA-00060: deadlock detected while waiting for resource
    ORA-06512: at "SCOTT.EMPID_TRIG", line 4
    ORA-04088: error during execution of trigger 'SCOTT.EMPID_TRIG'

  3. #3
    Join Date
    Jul 2011
    Posts
    9
    hi frnds,

    i have done this
    many thanks for ur supports
    here is the code

    create or replace trigger empID_trig
    before insert ON emp
    for each row
    begin
    IF :new.deptno = 10 then
    :new.ename := 'amitesh';
    end if;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •