How to Figure Out That a Column in My Oracle Table Is Being Populated/Updated by a Trigger of Another Table

Create trigger that updates row depending on column value from other table

As far as I understood is you want to update table2 only when the state in table1 changed to 'approved' for a row and if a row is inserted in table1 trigger will insert the row in table2.

I have made some corrections to your code. Let me know if it is not what you wanted.

CREATE OR REPLACE TRIGGER INSERT_PAGE 
    BEFORE UPDATE OR INSERT
    ON TABLE1 
    FOR EACH ROW
    DECLARE 
    BEGIN
        IF INSERTING THEN 
                INSERT INTO TABLE2 (TBL1ID,STEP,PAGE) VALUES 
                (:NEW.TBL1ID,:NEW.STEP,15); 
      
        ELSIF UPDATING THEN 
          
            IF :NEW.STATE = 'APPROVED' THEN 
          
               UPDATE table2 t2 SET
               STATE = :NEW.STATE, PAGE=16, STEP1='TEXT123'      
               WHERE t2.TBL1ID = :OLD.TBL1ID;
            
            END IF;
          
        END IF; 
    END; 

Query or method to find out packages depending on a particular column of a table

You will have to look into all_dependencies view to check what all objects are using your table.

select * from all_dependencies 
where referenced_name = '<table_name_in_upper_case>';

But this does not tell you what columns are being used.

You'll have to look into all_source for that.

select distinct type, name 
from all_source 
where upper(text) like '<column_name_in_upper_case>'
and name in (
            select name 
            from all_dependencies 
            where referenced_name = '<table_name_in_upper_case>'
            );

However this may give you false positives, if the column name is generic.

Also, all_source won't show you any views that might be using this column name. You will have to look into all_views or use

select dbms_metadata.get_ddl('VIEW','view_name_in_upper_case','owner_in_upper_case')
FROM dual

Oracle: SQL query to find all the triggers belonging to the tables?

The following will work independent of your database privileges:

select * from all_triggers
where table_name = 'YOUR_TABLE'

The following alternate options may or may not work depending on your assigned database privileges:

select * from DBA_TRIGGERS

or

select * from USER_TRIGGERS

How to find out when an Oracle table was updated the last time

Since you are on 10g, you could potentially use the ORA_ROWSCN pseudocolumn. That gives you an upper bound of the last SCN (system change number) that caused a change in the row. Since this is an increasing sequence, you could store off the maximum ORA_ROWSCN that you've seen and then look only for data with an SCN greater than that.

By default, ORA_ROWSCN is actually maintained at the block level, so a change to any row in a block will change the ORA_ROWSCN for all rows in the block. This is probably quite sufficient if the intention is to minimize the number of rows you process multiple times with no changes if we're talking about "normal" data access patterns. You can rebuild the table with ROWDEPENDENCIES which will cause the ORA_ROWSCN to be tracked at the row level, which gives you more granular information but requires a one-time effort to rebuild the table.

Another option would be to configure something like Change Data Capture (CDC) and to make your OCI application a subscriber to changes to the table, but that also requires a one-time effort to configure CDC.

Compound trigger to insert into another table after insert or update into the main table

There doesn't appear to be a need to use a compound trigger here because there is no need to query the person table. Just use a row-level trigger and reference the :new and :old pseudorecords in your logic

create or replace trigger trg_biur_person
  before insert or update on person
  for each row
begin
  IF :new.woman_act = 'X'
  THEN
    INSERT INTO v_changes(
                    id,
                    person_id, 
                    reg_number, 
                    last_name, 
                    first_name, 
                    dob, 
                    b_country, 
                    cit_country, 
                    poe, 
                    is_woman_act
                )
     SELECT  
                    v_changes_seq.nextval,
                    :new.id, --personId
                    :new.reg_number, 
                    :new.last_name, 
                    :new.first_name, 
                    :new.dob, 
                    (select cc1.valid_code
                       from country_code cc1
                      where cc1.id = :new.birth_c_id), 
                    (select cc2.valid_code
                       from country_code cc2
                      where cc2.id = :new.cit_country_id), 
                    (select poe.valid_code
                       from loc_code poe
                      where poe.poe_id = :new.birth_c_id), 
                    (case :new.woman_act when 'X' then 1 else 0 end)
    from dual;
  end if;
end;

Assuming you're just learning PL/SQL, it may be easier to create some local variables and use those in your insert statement, i.e.

create or replace trigger trg_biur_person
  before insert or update on person
  for each row
declare
  l_b_country v_changes.b_country%type;
begin
  begin
    select cc1.valid_code
      into l_b_country
      from country_code cc1
     where cc1.id = :new.birth_c_id;
  exception
    -- The fact that you're trying to do a `left join` makes me believe
    -- that you want to allow someone to insert a row with a `birth_c_id`
    -- that doesn't exist in the `country_code` table.  That doesn't make
    -- sense to me but that's what I'm going with.  If that isn't a valid 
    -- use case, you can omit the exception handler
    when no_data_found
    then
      l_b_country := null;
  end;

  ...

  insert into v_changes ...
    values( ..., l_b_country, ... );
end;

---

Related Topics