Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 453
  • Last Modified:

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

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
sikyala
Asked:
sikyala
  • 3
  • 2
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sikyalaSenior Database AdministratorAuthor Commented:
how would I do that?
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now