Solved

IF condition when defining after update trigger

Posted on 2014-01-06
17
376 Views
Last Modified: 2014-01-09
dear all,

I am now trying to create an after update trigger:

CREATE TRIGGER AB_UPDATE_TR AFTER UPDATE ON AB
FOR EACH ROW
BEGIN 

INSERT INTO DBAuditLog.AB_Audit SET 
TriggerAction='AFTER',
Action='UPDATE',
ActionDate=now(),
ActionBy=USER(),
i=NEW.i,
vendor=NEW.vendor,
sku=NEW.sku;

END

Open in new window


but can I add the if and ELSEIF as a condition check for that trigger?

CREATE TRIGGER AB_UPDATE_TR AFTER UPDATE ON AB
FOR EACH ROW
BEGIN 

INSERT INTO DBAuditLog.AB_Audit SET 
TriggerAction='AFTER',
Action='UPDATE',
ActionDate=now(),
ActionBy=USER(),

if i <> NEW.i then 
i=NEW.i;
end if;

if vendor <> NEW.vendor then
vendor=NEW.vendor;
END IF;

if sku <> NEW.sku then
sku=NEW.sku;
END IF;

END

Open in new window

0
Comment
Question by:marrowyung
  • 12
  • 5
17 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
you can use CASE construct, but from what you posted so far, I understand you only want to "update" that field if really you get a different value.
in which case, you cannot do it like this ...
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
""update" that field if really you get a different value.

yes, exactly as we found the MySQL after update trigger will keep logging that update operation and value even the same UPDATE statement EXECUTE many time when nothing is update.d

if I can't do thing like this, then how to fix the script above?

I just see this: http://stackoverflow.com/questions/14847702/multiple-if-statements-in-mysql-trigger

this is something worth interest ! agree? but it is working well ?

I appreciate that you give fast response !!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the "update" logging will happen on a per row basis, and not per column basis.
I don't consider it worthful.
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
what I tested is, if the update result set has 32 rows by the where cause, then the after update trigger will log the whole 32 records EVEN the before and after update value are the same !!


this surprise us a lot and we are try to seeing how to solve it.
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
Comment Utility
I see. you can do this:  
if i <> NEW.i then  OR  vendor <> NEW.vendor OR sku <> NEW.sku then
INSERT INTO DBAuditLog.AB_Audit SET 
TriggerAction='AFTER',
Action='UPDATE',
ActionDate=now(),
ActionBy=USER(),
i=NEW.i,
vendor=NEW.vendor,
sku=NEW.sku;
END IF ; 

Open in new window

0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
so this script will only do the OR / AND condition for ANY combination of the field if only 1, 2 of them has the real field update?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
but my example should works too ?

CREATE TRIGGER AB_UPDATE_TR AFTER UPDATE ON AB
FOR EACH ROW
BEGIN 

INSERT INTO DBAuditLog.AB_Audit SET 
TriggerAction='AFTER',
Action='UPDATE',
ActionDate=now(),
ActionBy=USER(),

if i <> NEW.i then 
i=NEW.i;
end if;

if vendor <> NEW.vendor then
vendor=NEW.vendor;
END IF;

if sku <> NEW.sku then
sku=NEW.sku;
END IF;

END 

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
no, your code will not work the way you want to have it, because the INSERT will run anyhow, and not only in the scenario you want it to run.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 1

Author Comment

by:marrowyung
Comment Utility
will this works:

INSERT INTO DBAuditLog.AB_Audit SET 
TriggerAction='AFTER',
Action='UPDATE',
ActionDate=now(),
ActionBy=USER(),
if i <> NEW.i then  OR  vendor <> NEW.vendor OR sku <> NEW.sku then
i=NEW.i,
vendor=NEW.vendor,
sku=NEW.sku;
END IF ;  

Open in new window


I move the if downward.  or just because everything write at once so I can't do this and the if has to include triggeraction, action and so on?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
The reason I said is I will run that using a function as the MySQL can't accept executing another SP inside a SP:

CREATE FUNCTION `Function_Create_AFTER_UPDATE_TRIGGER` (Trigger_databasename VARCHAR(40), trigger_tablename VARCHAR(40))  	
RETURNS TEXT
NOT DETERMINISTIC      
READS SQL DATA
BEGIN  
DECLARE a, b, fieldnameCursor_finished  INT DEFAULT 10;
DECLARE temptable VARCHAR(50);	
DECLARE today TIMESTAMP DEFAULT CURRENT_DATE;
DECLARE v1, v2, v3 TINYINT ;
DECLARE current_fieldname  VARCHAR(50) DEFAULT ""; 
DECLARE fullexcutecmd TEXT;
DECLARE fullexcutecmd1 TEXT;
DECLARE fullexcutecmd2 TEXT;
DECLARE fullexcutecmd3 TEXT;

/*Declare and populate the cursor with a SELECT statement */  	
	  
DECLARE fieldname CURSOR FOR 	
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = Trigger_databasename AND TABLE_NAME = trigger_tablename ;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fieldnameCursor_finished = 1;     
 
SET @fullexcutecmd1= CONCAT( ' DROP TRIGGER IF EXISTS ',trigger_tablename,'_AFTER_UPDATE_TRIGGER; CREATE TRIGGER ',trigger_tablename,'_AFTER_UPDATE_TRIGGER AFTER UPDATE ON ',trigger_tablename,' FOR EACH ROW
BEGIN 
INSERT INTO Audit_info.',Trigger_databasename,'_DBAudit_',trigger_tablename,' SET 
TriggerAction=','"','AFTER','",','
ActionDone=','"','UPDATE','",','
ActionDate=now(),
ActionBy=USER()');   

OPEN fieldname ;	
set @fullexcutecmd2=' ';
set @fullexcutecmd3=' ';

	get_fieldlist: LOOP	
 
   FETCH fieldname INTO current_fieldname;	
         IF fieldnameCursor_finished = 1 THEN 	
          LEAVE get_fieldlist;	
          END IF;	
 
SET @fullexcutecmd2 = CONCAT(' ',current_fieldname,'=NEW.',current_fieldname);
 set @fullexcutecmd3= CONCAT(@fullexcutecmd3,',',@fullexcutecmd2); 
   
      END LOOP  get_fieldlist;	
close fieldname;                             
  
  set @fullexcutecmd3= CONCAT(@fullexcutecmd1,@fullexcutecmd3,'; END  ;'); 

END $$
DELIMITER ; 

Open in new window


so I accept the database name and tablename from the SP and this function will only check all column on that table and loop it one by one to create that after update trigger statement and generate it for us to run it in another MySQL console.

then it is hard for me to find out all column one by one bfeore the drop trigger statement :

SET @fullexcutecmd1= CONCAT( ' DROP TRIGGER IF EXISTS ',trigger_tablename,'_AFTER_UPDATE_TRIGGER; CREATE TRIGGER ',trigger_tablename,'_AFTER_UPDATE_TRIGGER AFTER UPDATE ON ',trigger_tablename,' FOR EACH ROW
BEGIN 

Open in new window


agree? it is impossible to fix it, right ?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
remember that all table has different column and if I loop that out and it is hard to fit in this:

if i <> NEW.i then  OR  vendor <> NEW.vendor OR sku <> NEW.sku then

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
INSERT as such is 1 single sql statement, while IF is a control flow statement part.
you cannot put IF into a sql statement. full stop.

what you can do (what I wrote above) is to put a IF ... END IF around 1 (or several) sql statement(s), in your case 1 single insert statement.

but I don't see any issue with your code being rewritten so that you build the "if" statement dynamically the same way as you try currently, except that the code goes BEFORE the insert part instead of behind ...
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
one thing, I recieve error:

should this one :

if i <> NEW.i then  OR  vendor <> NEW.vendor OR sku <> NEW.sku then

Open in new window


change to

if i <> NEW.i OR  vendor <> NEW.vendor OR sku <> NEW.sku then

Open in new window


there are 2x then, right?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
I am receiveing error when the after update trigger is workging fine:

DROP TRIGGER IF EXISTS ACCOUNT_20130915_AFTER_UPDATE_TRIGGER; 
CREATE TRIGGER ACCOUNT_20130915_AFTER_UPDATE_TRIGGER AFTER UPDATE 
ON ACCOUNT_20130915 
FOR EACH ROW 
BEGIN  
if bigint_added_forAudittest <> NEW.bigint_added_forAudittest OR  
CUSTOMER_ID <> NEW.CUSTOMER_ID OR 
DEBTOR_ID <> NEW.DEBTOR_ID OR
REF_BILLING_ID <> NEW.REF_BILLING_ID OR
ACCT_NO <> NEW.ACCT_NO OR
ACCOUNT_NAME <> NEW.ACCOUNT_NAME OR
BILLING_ADDRESS <> NEW.BILLING_ADDRESS OR
BILLING_COUNTRY_ID <> NEW.BILLING_COUNTRY_ID OR
ATTENTION <> NEW.ATTENTION OR
PHONE_NO <> NEW.PHONE_NO OR
EMAIL <> NEW.EMAIL OR
REMAIL <> NEW.REMAIL OR
FAX_NO <> NEW.FAX_NO OR
VarChar_added_forAudittest <>NEW.VarChar_added_forAudittest OR
CREATED_DT <> NEW.CREATED_DT OR
CREATED_BY<> NEW.CREATED_BY OR
UPDATED_DT<>NEW.UPDATED_DT OR
UPDATED_BY <> NEW.UPDATED_BY OR
IS_OBSOLETE <> NEW.IS_OBSOLETE OR
integer_added_forAudittest <> NEW.integer_added_forAudittest
then
INSERT INTO 
Audit_info.PCCWSP_DBAudit_ACCOUNT_20130915 SET  
TriggerAction="AFTER", ActionDone="UPDATE", 
ActionDate=now(), ActionBy=USER() , 
bigint_added_forAudittest=NEW.bigint_added_forAudittest, 
CUSTOMER_ID=NEW.CUSTOMER_ID, 
DEBTOR_ID=NEW.DEBTOR_ID, 
REF_BILLING_ID=NEW.REF_BILLING_ID, 
ACCT_NO=NEW.ACCT_NO, 
ACCOUNT_NAME=NEW.ACCOUNT_NAME, 
BILLING_ADDRESS=NEW.BILLING_ADDRESS, 
BILLING_COUNTRY_ID=NEW.BILLING_COUNTRY_ID, 
ATTENTION=NEW.ATTENTION, 
PHONE_NO=NEW.PHONE_NO, 
EMAIL=NEW.EMAIL, 
REMAIL=NEW.REMAIL, 
FAX_NO=NEW.FAX_NO, 
VarChar_added_forAudittest=NEW.VarChar_added_forAudittest, 
CREATED_DT=NEW.CREATED_DT, 
CREATED_BY=NEW.CREATED_BY, 
UPDATED_DT=NEW.UPDATED_DT, 
UPDATED_BY=NEW.UPDATED_BY, 
IS_OBSOLETE=NEW.IS_OBSOLETE, 
integer_added_forAudittest=NEW.integer_added_forAudittest; 
END IF;
END  ; 

Open in new window


but when executing the update statement, it said:

The field bigint_added_forAudittest  is unknown

Open in new window


but I verify that this field exists for sure but it only appear aft I add that IF statement.

any idea?
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
or

Unknown column 'bigint_added_forAudittest' in 'field list'
0
 
LVL 1

Author Comment

by:marrowyung
Comment Utility
finally, after asking developer, this one works:

if OLD.bigint_added_forAudittest <> NEW.bigint_added_forAudittest OR  
OLD.CUSTOMER_ID <> NEW.CUSTOMER_ID OR 
OLD.DEBTOR_ID <> NEW.DEBTOR_ID OR
OLD.REF_BILLING_ID <> NEW.REF_BILLING_ID OR
OLD.ACCT_NO <> NEW.ACCT_NO OR
OLD.ACCOUNT_NAME <> NEW.ACCOUNT_NAME OR
OLD.BILLING_ADDRESS <> NEW.BILLING_ADDRESS OR
OLD.BILLING_COUNTRY_ID <> NEW.BILLING_COUNTRY_ID OR
OLD.ATTENTION <> NEW.ATTENTION OR
OLD.PHONE_NO <> NEW.PHONE_NO OR
OLD.EMAIL <> NEW.EMAIL OR
OLD.REMAIL <> NEW.REMAIL OR
OLD.FAX_NO <> NEW.FAX_NO OR
OLD.VarChar_added_forAudittest <>NEW.VarChar_added_forAudittest OR
OLD.CREATED_DT <> NEW.CREATED_DT OR
OLD.CREATED_BY<> NEW.CREATED_BY OR
OLD.UPDATED_DT<>NEW.UPDATED_DT OR
OLD.UPDATED_BY <> NEW.UPDATED_BY OR
OLD.IS_OBSOLETE <> NEW.IS_OBSOLETE OR
OLD.integer_added_forAudittest <> NEW.integer_added_forAudittest

Open in new window


we must have the OLD and NEW before and after the field checking !~!
0
 
LVL 1

Author Closing Comment

by:marrowyung
Comment Utility
thansk you anyway !!
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
php survey script 4 57
CSV Data Import without Header Row 1 41
generate a dynamic mysql query 2 36
First name pregmatch 11 29
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now