Creating a Trigger and using SEQUENCE

[mnewsrx]

I am having problems with a PL/SQL oracle program. I am trying to invoke a trigger before UPDATE ON table Product and insert the trigger messages into the table Events. Both Product and Events tables have been created. Also, the sequence has been created.

Here's my code below:

CREATE OR REPLACE TRIGGER MY_TRIG
BEFORE UPDATE
ON PRODUCT
FOR EACH ROW
BEGIN
INSERT INTO EVENTS(Prod_SEQ.NEXTVAL, eventtime, userupdating :OLD.ProductID FROM Product);
END;
/

I keep getting the same 2 errors:

1/2 SQL statement ignored
1/69 Invalid user.table.column, table column or column specification

Can anyone help me please. Thank you in advance

 

[SantaMufasa]

MNew,

It would be helpful to see a DESCRIBE of both the EVENTS table and the PRODUCT table. I'm not sure what you are trying to do with the code "...userupdating :OLD.ProductID...". Notice that there is no comma separating those two expressions, and this INSERT syntax does not allow for column aliases. You also cannot use the clause, "...FROM Product" without making it part of a SELECT statement.

Without more information, I'll assert the following syntax to try to simply get past the syntax/run-time error that you are receiving:

CREATE OR REPLACE TRIGGER MY_TRIG 
BEFORE UPDATE   
ON PRODUCT  
FOR EACH ROW   
BEGIN   
INSERT INTO EVENTS(Prod_SEQ.NEXTVAL, eventtime, userupdating, :OLD.ProductID);   
END; 
/

The code, above, presumes that there are precisely four columns in the EVENTS table.

Let us know your thoughts on this.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[mnewsrx]

Thank you for your assistance. I reran the code exactly as you have it and got the same errors. The table Product has 4 columns(ProsuctID, Name, ListPrice, Category). I have entered 5 rows of data in the Product table. The table Events has 4 columns (EventID, ProductID, UserUpdating, EventTime). I want to create a trigger that will be invoked before update on Product. The trigger should insert messages to Events table. I want to use SEQUENCE to generate unique event IDS. So, I am looking for output in the Events table that look something like this:

1, 299, SYSTEM, 21-Apr-2011 21:18:27
2, 300, SYSTEM, 21-Apr-2011 23:20:50

I hope this is clearer. What's bugging me is that I keep getting the same compiliation errors. Thanks again for your assistance.

 

[LKBrwnDBA]

Maybe something like this?:

CREATE OR REPLACE TRIGGER MY_TRIG
  BEFORE UPDATE ON PRODUCT
  FOR EACH ROW
BEGIN
  INSERT INTO EVENTS 
     (Prod_SEQ.NEXTVAL, :OLD.ProductID
     ,'User update', SYSDATE);
END;
/

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[karluk]

The reason you are getting the invalid column error is that Oracle is expecting a list of the columns being inserted, but you have provided the name of the sequence that contains the value to be inserted. Probably what you need is something like

CREATE OR REPLACE TRIGGER MY_TRIG
BEFORE UPDATE
ON PRODUCT
FOR EACH ROW
BEGIN
INSERT INTO EVENTS (EventID, ProductID, UserUpdating, EventTime)
	    values (Prod_SEQ.NEXTVAL, :OLD.ProductID, user, sysdate);
end;

 

[mnewsrx]

No, that did not work as well. I get the same 2 errors:

2/1 SQL Statement Ignored
2/39 Invalid user.table.column, table.column or column specification

Here are my columns for Product:
ProductID, Name, ListPrice, Category

Here are my columns for Events:
EventID, ProductID, UserUpdating, EventTime

 

[mnewsrx]

Karluk,

Thank you very much. It worked this time. Thanks again.