triggers [message #274349] |
Mon, 15 October 2007 13:17  |
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   |
Frank
Messages: 7901 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   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
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  |
 |
Littlefoot
Messages: 21823 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;
|
|
|