Home » SQL & PL/SQL » SQL & PL/SQL » Error
Error [message #293718] Mon, 14 January 2008 14:35 Go to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

I am trying to create this trigger, but it is creating with error. Is anybody make the correction, if anything wrong in the code. The tables which i am using in this trigger, they all are existing.

CREATE OR REPLACE TRIGGER before_employee_salary_update
    BEFORE UPDATE OF salary
    ON employee
    FOR EACH ROW WHEN (new.salary < old.salary * 0.75)
    BEGIN
      dbms_output.put_line('id = ' || :old.id);
      dbms_output.put_line('Old salary = ' || :old.salary);
      dbms_output.put_line('New salary = ' || :new.salary);
      dbms_output.put_line('The salary reduction is more than 25%');
    INSERT INTO Myaudit (
     id, old_value, new_value
   ) VALUES (
     :old.id, :old.salary, :new.salary
   );
   END before_employee_salary_update;
   /
Re: Error [message #293720 is a reply to message #293718] Mon, 14 January 2008 14:42 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Read & FOLLOW posting guidelines as stated in URL above.
>I am trying to create this trigger, but it is creating with error.
Error? What error? I don't see any error so I have nothing to fix.

[Updated on: Mon, 14 January 2008 14:43] by Moderator

Report message to a moderator

Re: Error [message #293721 is a reply to message #293718] Mon, 14 January 2008 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no error in what you posted.
How do you expect we know it? Do you think we are synatx analyzer or PL/SQL compiler?

Regards
Michel
Re: Error [message #293724 is a reply to message #293720] Mon, 14 January 2008 14:47 Go to previous messageGo to next message
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
Member

Sorry i missed that part earlier. I do oplogize for it..
PLS-00049: bad bind variable 'OLD.ID'
Re: Error [message #293727 is a reply to message #293724] Mon, 14 January 2008 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Where?
Can't you use SQL*Plus and copy and paste the screen WITH line numbers?
Are you sure ID column exists?
Post a description of the table.

Regards
Michel

Re: Error [message #293731 is a reply to message #293718] Mon, 14 January 2008 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
qasim845 ,
You need to keep in mind & realize we can ONLY see what you post.
It would make it easier if you would use CUT & PASTE to show us EXACTLY what you are doing & how Oracle is responding.
Re: Error [message #293903 is a reply to message #293718] Tue, 15 January 2008 10:15 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
I am not prety much sure about you are error ,But I love the Guessing.May be u are referening in the insert statement a wrong column .
See below how it is used.

SQL> desc  emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPID                                              NUMBER(4)
 EMPNAME                                            VARCHAR2(10)
 SUPID                                              NUMBER(4)
 DEPTNO                                             VARCHAR2(4)

SQL> select * from emp;

     EMPID EMPNAME         SUPID DEPT
---------- ---------- ---------- ----
       100 anu                 1 10
         1 sid                50 20
        50 sagar             100 50
         2 Rahul            1000 10
         3 Rai               200 10
         4 Raj               300 20
         5 Ram               300 10
         1 rao               200 10
         6 CRA               400 10
Now create a table Myaudit

drop table Myaudit;

create table Myaudit(id number,old_name varchar2(100),new_sup varchar2(10));


write a trigger emp table which will insert the data in to Myaudit table.

SQL> CREATE OR REPLACE TRIGGER before_employee_salary_update
  2      BEFORE UPDATE OF supid
  3      ON emp
  4      FOR EACH ROW WHEN (new.supid < old.supid)
  5      BEGIN
  6        dbms_output.put_line('id = ' || :old.supid);
  7        dbms_output.put_line('Old name = ' || :old.empname);
  8        dbms_output.put_line('New salary = ' || :new.supid);
  9        dbms_output.put_line('The salary reduction is more than 25%');
 10      INSERT INTO Myaudit (
 11       id, old_name, new_sup
 12     ) VALUES (
 13       :old.supid, :old.empname, :new.supid
 14     );
 15     END before_employee_salary_update;
 16  /

Trigger created.

SQL> show err

[Updated on: Tue, 15 January 2008 10:23] by Moderator

Report message to a moderator

Re: Error [message #293907 is a reply to message #293903] Tue, 15 January 2008 10:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Instead of trying to guess you should use the preview button your post was a mess and you also should read the guidelines and avoid IM speak. Don't you already know this after almost 50 posts?

Regards
Michek
Re: Error [message #293914 is a reply to message #293718] Tue, 15 January 2008 10:37 Go to previous messageGo to next message
ramanajv1968
Messages: 168
Registered: December 2005
Location: HYDERABAD
Senior Member
hi Michel,

Several times I used to get help from you people ,
and I never helped any one so far.
so , i am searching for an opertunity and i got this opertunity to post the reply.In the hurry i missed and mess-up the things Razz .

Definately, will not be repeat these in next postings.
Re: Error [message #293925 is a reply to message #293914] Tue, 15 January 2008 11:03 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
But don't stop trying to help even if you think my posts are harsh. Nod

Regards
Michel
Previous Topic: creating procedure to move data from one table to other in modified format
Next Topic: Dynamic SQL in SP
Goto Forum:
  


Current Time: Tue Dec 06 00:09:21 CST 2016

Total time taken to generate the page: 0.09009 seconds