How to make a trigger work after a certain UPDATE

oracletrigger

I would like to know if it's possible to create a trigger on Oracle that is invoked only when a certain UPDATE is done. For example:

UPDATE colors
SET tastes_good = y
WHERE color_name = 'Apple';

should call the trigger, while

UPDATE colors
SET tastes_good = n
WHERE color_name = 'Apple';

should not.

Best Answer

Something like this will suit your needs:

create or replace trigger colors_utrig
after update on colors
for each row
begin
  if :new.tastes_good = 'y'
    then
      -- put your code here
      NULL;
  end if;
end;
/

Related Solutions

Mysql – Trigger to UPDATE after UPDATE

Use a BEFORE trigger instead, and set the updated column assigning a value to NEW.updated (this would be a simple assignment, not an UPDATE). This way you won't trigger additional UPDATEs.

Your trigger body will simply look like

SET NEW.updated = NOW()

I usually use AFTER triggers only for modifying other tables, BEFORE for modifying the new (or updated) row, or suppress DELETE.

MySQL After Update Trigger apparently not running

My initial guess would be based on the trigger's flow. Why ?

According to the Book

MySQL Stored Procedure Programming

Chapter 11 Page 251 Paragraph 3 says the following:

The most significant difference between BEFORE and AFTER triggers is that in an AFTER you are not able to modify the values about to be inserted into or updated with the table in question -- the DML has executed, and it is too late to try to change what the DML is going to do.

From the this statement, this is what I understand: Since the DML is executed and the DML is still spoken of in the future imperfect tense (I am not an English teacher), the new values are really not available to be read in a SELECT since the trigger is not done.

In your case, you are doing an AFTER UPDATE on phpbb_rathermenacing.phpbb_bbdkp_memberlist. With the trigger being 99.99999999% done, you are attempting to do the following in the third clause of the IF statement:

        ((select min(m.member_rank_id) 
            from phpbb_bbdkp_memberlist m 
            WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                  m.member_guild_id = NEW.member_guild_id AND 
                  m.member_id != NEW.member_id) >= new.member_rank_id OR 
        (select count(m.member_rank_id) 
           from phpbb_bbdkp_memberlist m 
          WHERE m.phpbb_user_id = NEW.phpbb_user_id AND 
                m.member_guild_id = NEW.member_guild_id AND 
                m.member_id != NEW.member_id) = 0)

You are asking for the count and state of the table phpbb_bbdkp_memberlist that has not yet been changed. Regardless of the Storage Engine, I am very sure you will never get a response because the trigger has to finish in order for the Storage Engine to even think about posting changes to disk.

I have two old posts where I discuss why a trigger is no place for business intelligence:

Aug 11, 2011 : Policies RE database triggers in well-designed applications?
Aug 15, 2011 : Performance of a Trigger vs Stored Procedure in MySQL

SUGGESTIONS

Make that trigger a stored procedure
Remove the AFTER UPDATE trigger
Run the stored procedure after the UPDATE in your application.

Best Answer

Related Solutions

Mysql – Trigger to UPDATE after UPDATE

MySQL After Update Trigger apparently not running

SUGGESTIONS

Related Question