Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

IF condition when defining after update trigger

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

Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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 ...
Avatar of marrowyung
marrowyung

ASKER

""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 !!
the "update" logging will happen on a per row basis, and not per column basis.
I don't consider it worthful.
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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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

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.
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?
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 ?
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

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 ...
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?
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?
or

Unknown column 'bigint_added_forAudittest' in 'field list'
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 !~!
thansk you anyway !!