marrowyung
asked on
IF condition when defining after update trigger
dear all,
I am now trying to create an after update trigger:
but can I add the if and ELSEIF as a condition check for that trigger?
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
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
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 !!
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.
I don't consider it worthful.
ASKER
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.
this surprise us a lot and we are try to seeing how to solve it.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
ASKER
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
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.
ASKER
will this works:
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?
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 ;
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?
ASKER
The reason I said is I will run that using a function as the MySQL can't accept executing another SP inside a SP:
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 :
agree? it is impossible to fix it, right ?
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 ;
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
agree? it is impossible to fix it, right ?
ASKER
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
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 ...
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 ...
ASKER
one thing, I recieve error:
should this one :
change to
there are 2x then, right?
should this one :
if i <> NEW.i then OR vendor <> NEW.vendor OR sku <> NEW.sku then
change to
if i <> NEW.i OR vendor <> NEW.vendor OR sku <> NEW.sku then
there are 2x then, right?
ASKER
I am receiveing error when the after update trigger is workging fine:
but when executing the update statement, it said:
but I verify that this field exists for sure but it only appear aft I add that IF statement.
any idea?
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 ;
but when executing the update statement, it said:
The field bigint_added_forAudittest is unknown
but I verify that this field exists for sure but it only appear aft I add that IF statement.
any idea?
ASKER
or
Unknown column 'bigint_added_forAudittest ' in 'field list'
Unknown column 'bigint_added_forAudittest
ASKER
finally, after asking developer, this one works:
we must have the OLD and NEW before and after the field checking !~!
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
we must have the OLD and NEW before and after the field checking !~!
ASKER
thansk you anyway !!
in which case, you cannot do it like this ...