Home » SQL & PL/SQL » SQL & PL/SQL » Problem Involving Trigger
Problem Involving Trigger [message #275563] Sun, 21 October 2007 20:37 Go to next message
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 #275565 is a reply to message #275563] Sun, 21 October 2007 21:06 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Since the post was not formatted using <code tags> I may have miscounted;
but I counted 11 columns in the table & only 10 in the VALUES clause.
Re: Problem Involving Trigger [message #275567 is a reply to message #275563] Sun, 21 October 2007 21:13 Go to previous messageGo to next message
fuwath
Messages: 2
Registered: October 2007
Junior Member
I believe you may have miscounted. I have 11 in the Price_Audit table and 11 in with the insert values.

[Updated on: Sun, 21 October 2007 21:15]

Report message to a moderator

Re: Problem Involving Trigger [message #275568 is a reply to message #275563] Sun, 21 October 2007 21:19 Go to previous messageGo to next message
BlackSwan
Messages: 25046
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 #275569 is a reply to message #275563] Sun, 21 October 2007 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
OP has change the ORIGINAL post twice MATERIALLY since the first posting.

You're On Your Own (YOYO)!

[Updated on: Sun, 21 October 2007 22:10] 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 Go to previous messageGo to next message
Maaher
Messages: 7062
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
Re: Problem Involving Trigger [message #275867 is a reply to message #275563] Tue, 23 October 2007 01:59 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Can you please describe MyPrice table.

[Updated on: Tue, 23 October 2007 02:00]

Report message to a moderator

Previous Topic: Trigger in Function
Next Topic: Help needed in Materialized View
Goto Forum:
  


Current Time: Thu Dec 08 22:23:36 CST 2016

Total time taken to generate the page: 0.04987 seconds