Home » SQL & PL/SQL » SQL & PL/SQL » triggers
triggers [message #274349] Mon, 15 October 2007 13:17 Go to next message
krishna17
Messages: 1
Registered: July 2007
Junior Member
SQL> select * from employee;

NAME AGE SALARY
--------------------------- ---------- ----------
krishna 27 80000
teja 27 80000
sai 26 90000


SQL> create table emp_audit(name varchar(9),oldsalary number(9),newsalary number(9));

Table created.


SQL> create or replace trigger update_employee
2 after update of salary on employee for each row
3 begin
4 insert into emp_audit(:old.name,:old.salary,:new.salary);
5 end;
6 /

Warning: Trigger created with compilation errors.

SQL> show errors trigger update_employee;
Errors for TRIGGER UPDATE_EMPLOYEE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1 PL/SQL: SQL Statement ignored
2/23 PL/SQL: ORA-00928: missing SELECT keyword
SQL> update employee set salary=999999 where name='krishna';
update employee set salary=999999 where name='krishna'
*
ERROR at line 1:
ORA-04098: trigger 'KRISHNA.DEL_EMPLOYEE' is invalid and failed re-validation
Re: triggers [message #274351 is a reply to message #274349] Mon, 15 October 2007 13:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This may sound harsh, but there is a path to the light:
First learn sql, then pl/sql and only then start on triggers.
Re: triggers [message #274352 is a reply to message #274349] Mon, 15 October 2007 13:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I should first say
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel

[Updated on: Wed, 17 October 2007 01:01]

Report message to a moderator

Re: triggers [message #274674 is a reply to message #274349] Tue, 16 October 2007 15:15 Go to previous message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Basically, you've done everything OK, except of one missing keyword: VALUES.
CREATE OR REPLACE TRIGGER update_employee
  AFTER UPDATE OF salary ON employee
  FOR EACH ROW
BEGIN
  INSERT INTO emp_audit 
    (name, oldsalary, newsalary)            --> ALWAYS use column list!
     VALUES                                 --> that's what you've missed! 
    (:OLD.name, :OLD.salary, :NEW.salary);
END;
Previous Topic: Regarding Triggers & procedures (merged)
Next Topic: Threading in Pl/SQL
Goto Forum:
  


Current Time: Wed Dec 07 22:03:02 CST 2016

Total time taken to generate the page: 0.21349 seconds