Re: [mysql-175] Re: Dynamic Trigger

From: peterbvolk
Sent on: Wednesday, June 23, 2010 5:19 PM
Hi Eric,

not that I know of. But what you could do is create a trigger specific for each table with the explicit column names in it. Or, as I see that you are doing data versioning. Simply use the revision engine ;)

Cheers,
Peter

Am[masked]:35, schrieb Eric DeCoff:
Ideally the following would be better for a TRIGGER using @columnID

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;
            
        IF (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 ;

        IF (old.VALIDATED != new.VALIDATED) THEN
 
TO
        IF (old.@columnID != new.@columnID) THEN

On Wed, Jun 23, 2010 at 8:29 AM, Eric DeCoff <[address removed]> wrote:
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



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




--
Please Note: If you hit "REPLY", your message will be sent to everyone on this mailing list ([address removed])
This message was sent by Eric DeCoff ([address removed]) from The Atlanta MySQL Meetup Group.
To learn more about Eric DeCoff, visit his/her member profile
To unsubscribe or to update your mailing list settings, click here

Meetup, PO Box 4668 #37895 New York, New York[masked] | [address removed]

-- 
Peter Benjamin Volk
Project Lead

   DDEngine.org
   An open source project
   Phone:  [+49] (0)[masked]
   mailto:[address removed]
 

   Bunsenstraße 3  | D-01139 Dresden | GERMANY
***********************************************************
 
Diese Mail einschliesslich der Anhänge könnte vertraulich sein. Sollten Sie nicht der beabsichtigte Empfänger dieser Mail sein, so ist jegliches Verwenden, Offenbaren oder Kopieren der Mail und ihrer Anhänge untersagt. Bitte informieren Sie den Absender und löschen Sie die Mail und deren Anhänge von Ihrem Rechner. Vielen Dank. 
 
This email and its attachments may be confidential and/or privileged. If you are not the intended recipient, any use, disclosure or copying of this email and its attachments is prohibited. If you have received this email in error, please notify immediately the sender by return email and delete this email and its attachments from your computer system. Thank you.

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