Home » SQL & PL/SQL » SQL & PL/SQL » Trigger in support of a Staging Table on underlying table (Oracle 10g, XP)
Trigger in support of a Staging Table on underlying table [message #339131] Wed, 06 August 2008 19:33 Go to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
To all, any guidance you can provide is greatly appreciated.
My underlying table is A,
My staging table is B,
Both table structures col for col, except the stage table, table B includes columns for a flag to indicate record level changes ('I', 'U', or 'D') and create modified timestamp.

I thought it best to try and conquer new record inserts first.
Table A contains columns id (PK-sys generated #),NM, DEF_TX, ACRO_TX. You will notice below that I did not address the statement to ad an 'I' and Now() to populate the rec_status or c_mod_timestamp.

create trigger B_S_ID_SEQ_TR
after insert on A
for each row
begin
insert into B_s (
id,
NM,
DEF_TX,
ACRO_TX,
Rec_Status
C_mod_tmnstmp
(select id,
NM,
DEF_TX,
ACRO_TX,
from A);
END;
/

SQL> @d:\crsrv_s_id_seq_tr.sql
Trigger created.

Any way after many attempts, my syntax appeared to work. However, when i tested my work the following message displayed:
ORA-04091 Table A is mutating, trigger/function may not see it...B.B_S_ID_SEQ_TR... That was disappointing for sure; by the way I have never assigned triggers this complex. I only used triggers in support of sequences assigned.

Research tells me that i am trying to fire two triggers and they need to be in the proper sequence. I have looked at this all day and am lost in action to take. I have a trigger to fire in support of the PK sys generated in table A, somehow I need to create a trigger that will fire the trigger in order...

Does anyone have an idea how I would accomplish that and if possible point me in the right direction?

Thank you
very respectfully, Gen
Re: Trigger in support of a Staging Table on underlying table [message #339132 is a reply to message #339131] Wed, 06 August 2008 19:51 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow Posting Guideline as stated in URL above

>ORA-04091 Table A is mutating
I am sorry to see that both the Search function on this forum & GOOGLE are broken for you.

Please continue to wait for repairs to complete.
Additional will be made available after repairs have been completed.

[Updated on: Wed, 06 August 2008 19:57] by Moderator

Report message to a moderator

Re: Trigger in support of a Staging Table on underlying table [message #339133 is a reply to message #339132] Wed, 06 August 2008 20:01 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I will thanks, I noticed it on another post and began to read it. I also stumbled on the oracle reference links that are extremely helpful, I'm reviewing them now. I may not be all of this, but today was a good day to write a script with know syntax errors. I was handed this project to day, though a bit intimidating I am looking forward to it.
Re: Trigger in support of a Staging Table on underlying table [message #339163 is a reply to message #339131] Wed, 06 August 2008 23:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Application Developer's Guide - Fundamentals
Chapter 9 Coding Triggers

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Trigger in support of a Staging Table on underlying table [message #339408 is a reply to message #339163] Thu, 07 August 2008 07:55 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Thanks for the additional information and the instructions provided by Scott Mackey are very handy. I really want to get a handle on this, I'll be reading up on the information found and provided. I see where my approach taken is not the best. If I become stuck in my attempt, I’ll post and request pointers. I looked at many blogs before joining this one and you all should know this is certainly the best. I’ll learn the rules. This challenge came up and though intimidating at the moment, this will be a great experience. The only downfall is that I have no one to share ideas on how to implement.
Thanks again!
Re: Trigger in support of a Staging Table on underlying table [message #339425 is a reply to message #339408] Thu, 07 August 2008 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
The only downfall is that I have no one to share ideas on how to implement.

We prefer to help you to find it by yourself.

Regards
Michel
Re: Trigger in support of a Staging Table on underlying table [message #339565 is a reply to message #339131] Thu, 07 August 2008 18:47 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I concur, thanks!!
Re: Trigger in support of a Staging Table on underlying table [message #340436 is a reply to message #339425] Tue, 12 August 2008 19:19 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
I read in another post you should provide the solution, well here it is... It took me a bit read up on the material and try it out, but nevertheless I got it.


CREATE OR REPLACE TRIGGER s_Id_seq_tr
    AFTER UPDATE ON a
    FOR EACH ROW
    WHEN ( New.Id = Old.Id )
    BEGIN
      INSERT INTO s
                 (s_Id,
                  Id,
                  nm,
                  def_tx,
                  acro_tx,
                  dev_st_nm,
                  vrs_Id,
                  chng_cd,
                  c_mod_tmnstmp)
      VALUES     (Id_seq_s.Nextval,
                  :new.Id,
                  :new.nm,
                  :new.def_tx,
                  :new.acro_tx,
                  :new.dev_st_nm,
                  :new.vrs_Id,
                  'U',
                  SYSDATE);
    END;
/


Thanks!
Re: Trigger in support of a Staging Table on underlying table [message #340454 is a reply to message #340436] Wed, 13 August 2008 00:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I read in another post you should provide the solutio

From OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Trigger in support of a Staging Table on underlying table [message #340456 is a reply to message #340454] Wed, 13 August 2008 00:20 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Michel,

It was the original poster who provided the solution he ultimately worked out. He is not providing a solution to someone else, so this is in compliance with recommended guidelines.
Re: Trigger in support of a Staging Table on underlying table [message #340458 is a reply to message #340456] Wed, 13 August 2008 00:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oooh! I misread his post, I thought he said we should provide the solution and not he, as OP, should provide the solution when he found it.

I offer my genuine apologies for this.

Regards
Michel
Re: Trigger in support of a Staging Table on underlying table [message #340666 is a reply to message #339131] Wed, 13 August 2008 13:43 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Just a hint, you can make a single trigger handle all the delete, insert and update actions. There are keywords in triggers that can indicate the action that the trigger was fired on. Read up on oracle triggers and see how it is done. one of the keywords is "if INSERTING then", find the rest yourself. Good luck.
Re: Trigger in support of a Staging Table on underlying table [message #343652 is a reply to message #340666] Wed, 27 August 2008 14:23 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Bill B, I have taken your advice and built a trigger using the “if inserting (updating and deleting) then” statement and I actually have it working, but I have a minor problem that I can’t seem to overcome. My update trigger fires and appears to work as expected, but inserts two rows in my stage table. This only happens for updated records.
My create statement for the stage table is as follows:
CREATE TABLE srv_s (
  srv_s_Id                 NUMBER NOT NULL,
  srv_Id                   NUMBER NOT NULL,
  srv_nm                   VARCHAR2(150) NOT NULL,
  srv_def_tx               VARCHAR2(2000) NULL,
  srv_acro_tx              VARCHAR2(50) NULL,
  srv_Port_nm              VARCHAR2(100) NULL,
  srv_Port_bndg_nm         VARCHAR2(100) NULL,
  srv_Port_Soap_Addr_tx    VARCHAR2(255) NULL,
  srv_bndg_typ_nm          VARCHAR2(100) NULL,
  srv_bndg_Soap_Styl_nm    VARCHAR2(8) NULL,
  dev_st_nm                VARCHAR2(25) NULL,
  srv_vrs_Id               VARCHAR2(12) NOT NULL,
  srv_chng_cd              VARCHAR2(1) NULL,
  mod_c_dt                 DATE NULL,
  d_src                    VARCHAR2(5) NULL,
  chng_desc_tx             VARCHAR2(100) NULL,
  Accept_chng_Flag         NULL,
  srv_bndg_Soap_trnsprt_tx VARCHAR2(255) NULL,
  CONSTRAINT srv_s_Id_pk PRIMARY KEY( srv_s_Id ))
  TABLESPACE DataTools;

=========================
my trigger statement...
=========================
CREATE OR REPLACE TRIGGER srv_s_Id_seq_tr
    AFTER INSERT OR UPDATE OR DELETE ON srv
    FOR EACH ROW
    DECLARE
      srv_chng_cd_v       srv_s.srv_chng_cd%TYPE;
       mod_c_dt_v          srv_s.mod_c_dt%TYPE;
       srv_Id_v            srv.srv_Id%TYPE;
       d_src_v             srv_s.d_src%TYPE;
       chng_desc_tx_v      srv_s.chng_desc_tx%TYPE;
       Accept_chng_Flag_v  srv_s.Accept_chng_Flag%TYPE;
    BEGIN
      IF InsertIng THEN
        srv_chng_cd_v := 'I';
        
        mod_c_dt_v := SYSDATE;
        
        d_src_v := 'CDT';
        
        Accept_chng_Flag_v := 'N';
        
        INSERT INTO srv_s
        VALUES     (srv_s_Id_seq.Nextval,
                    :new.srv_Id,
                    :new.srv_nm,
                    :new.srv_def_tx,
                    :new.srv_acro_tx,
                    :new.srv_Port_nm,
                    :new.srv_Port_bndg_nm,
                    :new.srv_Port_Soap_Addr_tx,
                    :new.srv_bndg_typ_nm,
                    :new.srv_bndg_Soap_Styl_nm,
                    :new.dev_st_nm,
                    :new.srv_vrs_Id,
                    srv_chng_cd_v,
                    mod_c_dt_v,
                    d_src_v,
                    chng_desc_tx_v,
                    Accept_chng_Flag_v,
                    :new.srv_bndg_Soap_trnsprt_tx);
      ELSIF UpdAtIng THEN
        srv_chng_cd_v := 'U';
        
        d_src_v := 'CDT';
        
        chng_desc_tx_v := '';
        
        Accept_chng_Flag_v := 'N';
        
        IF nvl(:old.srv_nm,'null') != nvl(:new.srv_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'name';
        END IF;
        
        IF nvl(:old.srv_def_tx,'null') != nvl(:new.srv_def_tx,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'definition, ';
        END IF;
        
        IF nvl(:old.srv_acro_tx,'null') != nvl(:new.srv_acro_tx,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'acronym, ';
        END IF;
        
        IF nvl(:old.srv_Port_nm,'null') != nvl(:new.srv_Port_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'port name, ';
        END IF;
        
        IF nvl(:old.srv_Port_bndg_nm,'null') != nvl(:new.srv_Port_bndg_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'port binding name, ';
        END IF;
        
        IF nvl(:old.srv_Port_Soap_Addr_tx,'null') != nvl(:new.srv_Port_Soap_Addr_tx,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'port soap address, ';
        END IF;
        
        IF nvl(:old.srv_bndg_typ_nm,'null') != nvl(:new.srv_bndg_typ_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'binding type name, ';
        END IF;
        
        IF nvl(:old.srv_bndg_Soap_Styl_nm,'null') != nvl(:new.srv_bndg_Soap_Styl_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'binding soap style name, ';
        END IF;
        
        IF nvl(:old.dev_st_nm,'null') != nvl(:new.dev_st_nm,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'development status, ';
        END IF;
        
        IF nvl(:old.srv_vrs_Id,'null') != nvl(:new.srv_vrs_Id,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'version id, ';
        END IF;
        
        IF nvl(:old.srv_bndg_Soap_trnsprt_tx,'null') != nvl(:new.srv_bndg_Soap_trnsprt_tx,'null') THEN
          chng_desc_tx_v := chng_desc_tx_v
                            ||'binding soap transport text, ';
        END IF;
        
        UPDATE srv_s
        SET    mod_c_dt = SYSDATE
        WHERE  :new.srv_Id = :old.srv_Id;
        
        INSERT INTO srv_s
        VALUES     (srv_s_Id_seq.Nextval,
                    :new.srv_Id,
                    :new.srv_nm,
                    :new.srv_def_tx,
                    :new.srv_acro_tx,
                    :new.srv_Port_nm,
                    :new.srv_Port_bndg_nm,
                    :new.srv_Port_Soap_Addr_tx,
                    :new.srv_bndg_typ_nm,
                    :new.srv_bndg_Soap_Styl_nm,
                    :new.dev_st_nm,
                    :new.srv_vrs_Id,
                    srv_chng_cd_v,
                    mod_c_dt_v,
                    d_src_v,
                    chng_desc_tx_v,
                    Accept_chng_Flag_v,
                    :new.srv_bndg_Soap_trnsprt_tx);
      ELSIF DeleTing THEN
        srv_chng_cd_v := 'D';
        
        mod_c_dt_v := SYSDATE;
        
        d_src_v := 'CDT';
        
        Accept_chng_Flag_v := 'N';
        
        INSERT INTO srv_s
        VALUES     (srv_s_Id_seq.Nextval,
                    :old.srv_Id,
                    :old.srv_nm,
                    :old.srv_def_tx,
                    :old.srv_acro_tx,
                    :old.srv_Port_nm,
                    :old.srv_Port_bndg_nm,
                    :old.srv_Port_Soap_Addr_tx,
                    :old.srv_bndg_typ_nm,
                    :old.srv_bndg_Soap_Styl_nm,
                    :old.dev_st_nm,
                    :old.srv_vrs_Id,
                    srv_chng_cd_v,
                    mod_c_dt_v,
                    d_src_v,
                    chng_desc_tx_v,
                    Accept_chng_Flag_v,
                    :old.srv_bndg_Soap_trnsprt_tx);
      END IF;
    END;
/

==================================
Data output
==================================
SRV_S
SRV_S_ID|SRV_ID|SRV_NM|SRV_DEF_TX|SRV_ACRO_TX|SRV_PORT_NM|SRV_PORT_BNDG_NM|SRV_PORT_SOAP_ADDR_TX|SRV_BNDG_TYP_NM|SRV_BNDG_SOAP_STYL_N M|DEV_ST_NM|SRV_VRS_ID|SRV_CHNG_CD|MOD_C_DT|D_SRC|CHNG_DESC_TX|ACCEPT_CHNG_FLAG|SRV_BNDG_SOAP_TRNSPRT_TX
110|20|Award|An FPDS-NG service designed to allow users access to URLs, add new contract awards, and work with existing contract award information.||AwardPort|AwardBinding|https://www.fpds.gov/FPDS/GUIServices/DataCollection/contracts/1.3/Award|AwardPortType|RPC|Produ ction|08.01|U||CDT|acronym, |N|http://schemas.xmlsoap.org/soap/http
111|20|Award|An FPDS-NG service designed to allow users access to URLs, add new contract awards, and work with existing contract award information.||AwardPort|AwardBinding|https://www.fpds.gov/FPDS/GUIServices/DataCollection/contracts/1.3/Award|AwardPortType|RPC|Produ ction|08.01|U|8/27/2008 1:50:53 PM||||

Srv_s_id 110 is missing mod_c_dt, all other columns worked as expected…
Srv_s_id 111 should not have been inserted and only the mod_c_dt was added other columns (d_src, srv_chng_cd, …) declared in the trigger statement is null

===== Any clues to solve the problem is appreciated, after looking at it for so long I don't see it, thanks!

[Updated on: Wed, 27 August 2008 14:26]

Report message to a moderator

Re: Trigger in support of a Staging Table on underlying table [message #343654 is a reply to message #339131] Wed, 27 August 2008 14:30 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
YOu are both updating and inserting into your tracking table if you update. Is this what you wanted to do?
Re: Trigger in support of a Staging Table on underlying table [message #343657 is a reply to message #343654] Wed, 27 August 2008 14:43 Go to previous messageGo to next message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
My understanding of the requirement, yes.
Now I'm not sure... The customer wishes to see from the stage table
new records inserted
change records updated
deleted records
So to accomplish that I thought the trigger statement would need to address each action

[Updated on: Wed, 27 August 2008 14:48]

Report message to a moderator

Re: Trigger in support of a Staging Table on underlying table [message #343663 is a reply to message #343657] Wed, 27 August 2008 15:09 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
select from the table before you update and then select after. You should have a new record for the insert, for the update, and for the delete.
Re: Trigger in support of a Staging Table on underlying table [message #344105 is a reply to message #343663] Thu, 28 August 2008 15:23 Go to previous message
ora_newbie
Messages: 19
Registered: August 2008
Junior Member
Thanks! I removed the update statement from the ‘updating then’ segment, added the declare statement for mod_c_dt_v :=sysdate and once we realized another update trigger was firing in the background it finally worked. Thanks again for your help!
Previous Topic: null problem
Next Topic: MULTIPLE RECIENTS USING UTL_MAIL
Goto Forum:
  


Current Time: Fri Dec 09 00:07:39 CST 2016

Total time taken to generate the page: 0.05347 seconds