Solved

IF condition when defining after update trigger

Posted on 2014-01-06
17
389 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 5
17 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39761435
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
ID: 39761439
""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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39761451
the "update" logging will happen on a per row basis, and not per column basis.
I don't consider it worthful.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 1

Author Comment

by:marrowyung
ID: 39761463
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 39761524
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
ID: 39761538
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
ID: 39761539
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39761552
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39764153
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
ID: 39764164
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
ID: 39764165
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 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39765108
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
ID: 39767149
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
ID: 39767160
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
ID: 39767171
or

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

Author Comment

by:marrowyung
ID: 39767336
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
ID: 39767350
thansk you anyway !!
0

Featured Post

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

690 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