addressalign-toparrow-leftarrow-rightbackbellblockcalendarcameraccwchatcheckchevron-downchevron-leftchevron-rightchevron-small-downchevron-small-leftchevron-small-rightchevron-small-upchevron-upcircle-with-checkcircle-with-crosscircle-with-pluscrosseditemptyheartfacebookfolderfullheartglobegmailgoogleimagesinstagramlinklocation-pinmagnifying-glassmailminusmoremuplabelShape 3 + Rectangle 1outlookpersonplusprice-ribbonImported LayersImported LayersImported Layersshieldstartickettrashtriangle-downtriangle-uptwitteruseryahoo

Dynamic Trigger

From: Eric D.
Sent on: Wednesday, June 23, 2010 8:29 AM
Hey All,
?
Is there a way to do the following
?
DROP TRIGGER IF EXISTS UserAccount_Trigger_BeforeUpdate;
DELIMITER |
CREATE TRIGGER UserAccount_Trigger_BeforeUpdate
BEFORE UPDATE ON UserAccount
FOR EACH ROW
BEGIN
??? declare columnID bigint(20) DEFAULT 0;
??? declare columnName varchar(255);
??? declare maxOP bigint(20) DEFAULT 0;
???
??? SELECT max(ordinal_position) into @maxOP from information_schema.columns where table_name='UserAccount';
??? SELECT 0 into @columnID;
??? myLOOP:LOOP
???
??????? SELECT @columnID+1 INTO @columnID;
??????? IF (@columnID > @maxOP) THEN
??????????? LEAVE myLOOP;
??????? END IF;
???????????
??????? SELECT column_name INTO @columnName
??????????? FROM information_schema.columns
??????????? WHERE table_name='UserAccount' AND ordinal_position = @columnID;
???????
??????? IF (UCASE(@columnName) = 'VALIDATED' and old.VALIDATED != new.VALIDATED) THEN
??????????? INSERT INTO `useraccount_log` (RECORDID,ACTION,FIELD,OLD,NEW,USER) VALUES(new.ID,'UPDATED',@columnName,old.VALIDATED,new.VALIDATED,CURRENT_USER());
??????? END IF;
???????
??? END LOOP;
???
END|
DELIMITER ;
?
Can I change the following line to make?it dynamic?
?
??????? IF (UCASE(@columnName) = 'VALIDATED' and old.VALIDATED != new.VALIDATED) THEN
?
TO
??????? IF (old.@colunName != new.@columnName) THEN


--
Eric R. DeCoff
Changing the world,
1 line of code at a time

Our Sponsors

  • Sevaa Group Inc.

    Paying the meeetup fees as well as taking care of AtlantaMySQL.com!

People in this
Meetup are also in:

Sign up

Meetup members, Log in

By clicking "Sign up" or "Sign up using Facebook", you confirm that you accept our Terms of Service & Privacy Policy