Oracle SQL Trigger on Update of Column

Oracle SQL trigger on update of column

Use the WHEN clause:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  when (new.approved = 'Y')
  BEGIN
  :new.create_dt := sysdate;
  END;

Or use IF:

create or replace
  TRIGGER ADD_CREATE_DT 
  after UPDATE of approved ON articles 
  for each row
  BEGIN
  if :new.approved = 'Y' then
   :new.create_dt := sysdate;
  end if;
  END;

In this case, WHEN is more appropriate and efficient.

trigger to update specific column when insert/update happened in same table

Why not simply turn the trigger to a before trigger, when you can set the value before it is written? This way, you don't need to run a new DML statement on the table, which avoid the "mutating" error.

create or replace trigger user_change
after insert or update of email on user
for each row
begin
    if :new.email like '%@market.org.com' then
        :new.org := 'market';
    end if;
end;

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; 

How to execute trigger on delete or update on specific column happen in oracle?

Yes, IF can help.

Here's an example based on Scott's schema; have a look.

This is a log table:

SQL> create table deptb as select * From dept where 1 = 2;

Table created.

Trigger:

SQL> create or replace trigger trg_bdu_dept
  2    before delete or update on dept
  3    for each row
  4  begin
  5    if deleting then
  6      insert into deptb values (:old.deptno, :old.dname, :old.loc);
  7    elsif updating and :old.loc = 'NEW YORK' then
  8        insert into deptb values (:old.deptno, :old.dname, :old.loc);
  9    end if;
 10  end;
 11  /

Trigger created.

SQL>

Testing:

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NEW YORK
        20 RESEARCH             DALLAS
        30 SALES                CHICAGO
        40 OPERATIONS           BOSTON

SQL> delete from dept where deptno = 40;

1 row deleted.

SQL> update dept set loc = 'NY' where loc = 'NEW YORK';

1 row updated.

SQL> update dept set loc = 'dallas' where loc = 'DALLAS';

1 row updated.

SQL> select * from deptb;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        40 OPERATIONS           BOSTON
        10 ACCOUNTING           NEW YORK

SQL> select * from dept;

    DEPTNO DNAME                LOC
---------- -------------------- --------------------
        10 ACCOUNTING           NY
        20 RESEARCH             dallas
        30 SALES                CHICAGO

SQL>

How to update a column in same table using calculated value in oracle trigger

My assumption is that you are merely trying to update the particular row that caused the trigger to fire. And that actual_price is also coming from the row being modified. If so, you'd do that simply by assigning a value to the :new.subtotal. But you'd have to do it in a before update trigger not an after update trigger.

create or replace trigger your_trigger_name
  before update on your_table_name
  for each row
begin
  :new.subtotal := :new.quantity * :new.actual_price;
end;

If this is not a homework assignment, it would be more efficient and less error-prone to define subtotal as a computed column in the table rather than trying to maintain the data in a trigger.

oracle trigger after update on specific field followed by insert

The REFERENCING clause is just used to change the names of the built-in OLD and NEW records. OLD contains the values of the row before it was updated, and NEW holds the values after it was updated. Here's an example of how you might use them.

create table PM_USER_DATA (user_id varchar2(20), email varchar2(20), change_date date, changeby_char varchar2(20));
create table DEACTIVATED_USERS (username varchar2(20), email varchar2(20), deactivated_date date);

insert into pm_user_data values ('test', 'test@test.com', sysdate, 'SOMEUSER');

create or replace trigger deactivate_users
    after update of changeby_char on pm_user_data
    referencing old as o1 new as n1
    for each row
begin
    if :n1.changeby_char = 'BICSUPP' then
        insert into deactivated_users (username, email, deactivated_date)
            values (:n1.user_id, :n1.email, :n1.change_date);
    end if;
end;
/

update pm_user_data set changeby_char = 'BICSUPP';

select * from deactivated_users;   
/* output: 
USERNAME             EMAIL                DEACTIVATED_DATE
-------------------- -------------------- ----------------
test                 test@test.com        10-OCT-17       
1 row selected.
*/

Best practice for updating column specific triggers

As I understand your post, you have 4 live tables (called "table1", "table2", etc.) that you want to search on, but querying from them is too slow, so you want to maintain a single, flattened table to search on instead and have triggers to keep that flattened table always up-to-date.
You want to know which of two trigger approaches is better.

I think the answer is "neither", since both are prone to deadlocks. Imagine this scenario

User 1 -

UPDATE table1 
SET field_a = 500 
WHERE <condition effecting 200 distinct IDs>

User 2 at about the same time -

UPDATE table1 
SET field_b = 700 
WHERE <condition effecting 200 distinct IDs>

Triggers start processing. You cannot control the order in which the rows are updated. Maybe it goes like this:

User 1's trigger, time index 100 ->

UPDATE search_flat SET field_a = 500 WHERE id = 90;

User 2's trigger, time index 101 ->

UPDATE search_flat SET field_b = 700 WHERE id = 91;

User 1's trigger, time index 102 ->

UPDATE search_flat SET field_a = 500 WHERE id = 91;  (waits on user 2's session)

User 2's trigger, time index 103 ->

UPDATE search_flat SET field_b = 700 WHERE id = 90;  (deadlock error)

User 2's original update fails and rolls back.

You have multiple concurrent processes all updating the same set of rows in search_flat with no control over the processing order. That is a recipe for deadlocks.

If you wanted to do this safely, you should consider neither of the FOR EACH ROW trigger approaches you outlines. Rather, make a compound trigger to do this.

Here's some sample code to illustrate the idea. Be sure to read the comments.

-- Aside: consider setting this at the system level if on 12.2 or later
--   alter system set temp_undo_enabled=false;

CREATE GLOBAL TEMPORARY TABLE table1_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80),
  field_b     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE table2_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

-- .. so on for table3 and 4.

CREATE OR REPLACE TRIGGER table1_search_maint_trg
  FOR INSERT OR UPDATE OR DELETE ON table1  -- with similar compound triggers for table2, 3, 4.
    COMPOUND TRIGGER

  AFTER EACH ROW IS
  BEGIN
    -- Update the table-1 specific GTT with the changes.
    CASE WHEN INSERTING OR UPDATING THEN
      -- Assumes ID is immutable primary key
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:new.id, :new.field_a);
         WHEN DELETING THEN
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:old.id, null);  -- or figure out what you want to do about deletes.
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Write the data from the GTT to the search_flat table.
    -- NOTE: The ORDER BY in the next line is what saves us from deadlocks.
    FOR r IN ( SELECT id, field_a, field_b FROM table1_updates_gtt ORDER BY id ) LOOP
      -- TODO: replace with BULK processing for better performance, if DMLs can affect a lot of rows
      UPDATE search_flat sf
      SET    sf.field_a = r.field_a,
             sf.field_b = r.field_b
      WHERE  sf.id = r.id
      AND    ( sf.field_a <> r.field_a 
               OR (sf.field_a IS NULL AND r.field_a IS NOT NULL) 
               OR (sf.field_a IS NOT NULL AND r.field_a IS NULL)
               OR sf.field_b <> r.field_b 
               OR (sf.field_b IS NULL AND r.field_b IS NOT NULL) 
               OR (sf.field_b IS NOT NULL AND r.field_b IS NULL)
             );
    END LOOP;             
          
  END AFTER STATEMENT;

END table1_search_maint_trg;

Also, as numerous commenters have pointed out, it's probably better to use a materialized view for this. If you are on 12.2 or later, real-time materialized views (aka "ENABLE ON QUERY COMPUTATION") offer a lot of promise for this sort of thing. No COMMIT overhead to your application and real-time search results. It's just that search time degrades slightly if there are a lot of recent updates to the underlying tables.

---

Related Topics