| Problem Involving Trigger [message #275563] |
Sun, 21 October 2007 20:37  |
fuwath
Messages: 2
Registered: October 2007
|
Junior Member |
|
|
I'm a real newbie, so if I'm not giving enough information please ask me to ellaborate on something. What I'm trying to create is a trigger that fires when an update is performed on a table. The table is simply a :
CREATE TABLE MyPrice AS
SELECT *
FROM Scott.price;
The results of the trigger will be stored in an audit table like this:
CREATE TABLE Price_Audit (
Prodid NUMBER(7),
OldSTDPRICE VARCHAR2(10),
OldMINPRICE VARCHAR2(10),
OldSTARTDATE DATE,
OldENDDATE DATE,
NewSTDPRICE VARCHAR2(10),
NewMINPRICE VARCHAR2(10),
NewSTARTDATE VARCHAR2(10),
NewENDDATE DATE,
Changed_BY VARCHAR(10),
Changed_ON VARCHAR(10)
);
This is the trigger:
CREATE OR REPLACE TRIGGER Price_Audit_Trigger
BEFORE UPDATE ON MyPrice
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO Price_Audit VALUES(
Prodid,
:old.STDPRICE, :old.MINPRICE, :old.STARTDATE, :old.ENDDATE,
:new.STDPRICE, :new.MINPRICE, :new.STARTDATE, :new.ENDDATE,
user, sysdate);
END;
/
But when I create the trigger, I get Warning: Trigger created with compiliation errors. I show the errors and i get
2/3 PL/SQL: SQL Statement ignored
3/8 PL/SQL: ORA-00984: column not allowed here
Any help in a fix for my trigger would be greatly appreciated.
[Updated on: Sun, 21 October 2007 21:56] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Problem Involving Trigger [message #275568 is a reply to message #275563] |
Sun, 21 October 2007 21:19  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Please read the OraFAQ Forum Guide before posting.
It at the top every forum & points to URL below which among other things documents <code tags>
http://www.orafaq.com/forum/t/88153/0/
CREATE OR REPLACE TRIGGER Price_Audit_Trigger
BEFORE UPDATE
OF STDPRICE, MINPRICE, STARTDATE, ENDDATE ON MyPrice
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO Price_Audit VALUES(
1 Prodid,
2 :old.STDPRICE,
3 :old.MINPRICE,
4 :old.STARTDATE,
5 :old.ENDDATE,
6 :new.STDPRICE,
7 :new.MINPRICE,
8 :new.STARTDATE,
9 :new.ENDDATE,
10 user,
11 sysdate);
END;
/
Hmmm, formatting helps me count correctly.
If you use SQL*Plus in a terminal window it will point to the error. Use CUT & PASTE to show both your input & Oracle's response.
[Updated on: Sun, 21 October 2007 21:37] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: Problem Involving Trigger [message #275611 is a reply to message #275569] |
Mon, 22 October 2007 01:29 |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
Have a look at your trigger. What prodid are you inserting? And don't change your question anymore. This thread is very hard to follow if you keep changing the question in the opening post.
You asked why this trigger would not compile successfully:
CREATE OR REPLACE TRIGGER price_audit_trigger
BEFORE UPDATE ON myprice
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
INSERT INTO price_audit VALUES( Prodid --> ???????
, :OLD.STDPRICE
, :OLD.MINPRICE
, :OLD.STARTDATE
, :OLD.ENDDATE
, :NEW.STDPRICE
, :NEW.MINPRICE
, :NEW.STARTDATE
, :NEW.ENDDATE
, USER
, SYSDATE
);
END;
/
MHE
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
