Solved

What am I missing in a trigger that I am trying to create on a table

Posted on 2009-05-06
5
450 Views
Last Modified: 2013-12-11
I am trying to create a trigger that inserts a record in table c that is created on table b after dml is performed on table a. If that is confusing let me try this: there are 3 tables, table 1 has records, table 2 creates records everytime a user performs dml on table 1, table 2 is a log table that is used to refresh data in a fast refresh materialized view. So whenever a refresh occurs that table gets purged. I want to keep the records in that table. So I created another table (table 3) and I want the records in table 2 to be written to table 3 whenever a dml statement is done a record in table 1. I am not sure of what the most efficient way of doing this. I have tried the following:

create or replace trigger MVIEW_HOLDINGS_T
AFTER INSERT OR UPDATE OR DELETE ON HOLDINGS
begin
insert into FAST_UPDATE_INDEX (site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$,
 CHANGE_VECTOR$$) VALUES (site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$) from MLOG$_HOLDINGS where name.holdings=pdf_name.MLOG$_HOLDINGS and site.holdings=site.MLOG$_HOLDINGS;
end;
/

I type show errors and get the following: Errors for TRIGGER MVIEW_BIB_HOLDINGS_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
2/174    PL/SQL: ORA-00933: SQL command not properly ended

could someone tell me what is wrong?

create or replace trigger MVIEW_HOLDINGS_T
AFTER INSERT OR UPDATE OR DELETE ON HOLDINGS
begin
insert into FAST_UPDATE_INDEX (site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$,
 CHANGE_VECTOR$$) VALUES (site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$) from MLOG$_HOLDINGS where name.holdings=pdf_name.MLOG$_HOLDINGS and site.holdings=site.MLOG$_HOLDINGS;
end;
/

Open in new window

0
Comment
Question by:sikyala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24318482
not VALUES, but SELECT:
create or replace trigger MVIEW_HOLDINGS_T
AFTER INSERT OR UPDATE OR DELETE ON HOLDINGS
begin
insert into FAST_UPDATE_INDEX (site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$,
 CHANGE_VECTOR$$) 
  SELECT site, name, SNAPTIME$$, DMLTYPE$$, OLD_NEW$$, CHANGE_VECTOR$$ 
    FROM MLOG$_HOLDINGS 
   WHERE name.holdings=pdf_name.MLOG$_HOLDINGS 
     AND site.holdings=site.MLOG$_HOLDINGS;
end;
/

Open in new window

0
 

Author Comment

by:sikyala
ID: 24318563
I changed the trigger and got the following error:

Warning: Trigger created with compilation errors.

SQL> sho errors
Errors for TRIGGER MVIEW_BIB_HOLDINGS_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
3/176    PL/SQL: ORA-00904: "SITE"."MLOG$_HOLDINGS": invalid
         identifier

The site field does exist in the MLOG$_HOLDINGS table
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24318792
you are missing a join  in the query, indeed..
also, you don't refer to the NEW / OLD information to limit the rows to be inserted...
0
 

Author Comment

by:sikyala
ID: 24319319
how would I do that?
0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24319441
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question