skosterow has asked for the wisdom of the Perl Monks concerning the following question:

Greetings - all perl monks from past and present. Having a hell of a time trying to set a MySQL trigger using mysql-pp. I have a data base named ec_user. inside of that I have multiple tables. ec_user.login AND ec_user.user. BOTH contain the field: type. Trying to create a MySQL Trigger that when ec_user.login.type is updated MySQL will auto update ec_user.user.type. Here is the code I have so far: 
    $sql = "CREATE TRIGGER updt_type BEFORE UPDATE ON ec_user.login.type
        BEGIN
	    INSERT INTO ec_user.user.type
	    SET ec_user.user.type = NEW.ec_user.login.type,
	    tec_id = OLD.tec_id;
	END;
    ";
    $sth = $dbh->prepare($sql);
    $sth ->execute() or die "Connection Error: $DBI::errstr\n";
    $sth->finish();
    $dbh->disconnect();
I didn't include the MySQL-pp connection $dbh - I will, but I don't think its relevant....... When I run this is what I the error I get: (with strict enabled) 
DBD::mysqlPP::st execute failed: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.type
                BEGIN
                        INSERT INTO ec_user.user.type
                        SET ec_user.user.type = '',
                ' at line 1 at tables/user.pm line 106.
Connection Error: #42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.type
                BEGIN
                        INSERT INTO ec_user.user.type
                        SET ec_user.user.type = '',
                ' at line 1
Compilation failed in require at C:\inetpub\wwwroot\TEC\++ Database ++\db\db.pl line 250.
Thanks for the help in advanced!

Replies are listed 'Best First'.
Re: MySQL-pp, Perl and MySQL Triggers
by Mr. Muskrat (Canon) on Dec 18, 2015 at 21:16 UTC
[reply]
Re: MySQL-pp, Perl and MySQL Triggers
by GotToBTru (Prior) on Dec 18, 2015 at 21:00 UTC

    Would the sql you create work if you typed it into a MySQL command session? I'm guessing not since you are getting syntax errors. You appear to have an SQL problem, not a perl problem.

    Dum Spiro Spero
[reply]
      GotToBTru, Thats very possible Ill try it now.
[reply]
Re: MySQL-pp, Perl and MySQL Triggers
by poj (Abbot) on Dec 18, 2015 at 20:32 UTC

    Do the 2 tables have primary keys ?

    Assuming the keys are tec_id, try this

    my $sql = 'CREATE TRIGGER updt_type BEFORE UPDATE ON ec_user.login
           FOR EACH ROW
           BEGIN
             UPDATE ec_user.user
             SET type = NEW.type 
             WHERE tec_id=OLD.tec_id;
           END;';

    [download]
    poj
[reply]
[d/l]
[select]
      Yes, Both have a field called ID, that is primary. Here is the Table creation code: 
          $sql = "create table login (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	created DATE,
        tec_id VARCHAR(15),
	type VARCHAR(50),
	password VARCHAR(100),
	stage_name VARCHAR(150),
	last_log DATE,
	last_time TIME,
        active VARCHAR(3),
        banned VARCHAR(3),
	PRIMARY KEY (id),
	INDEX (`tec_id` ASC),
	INDEX (`type` ASC),
	INDEX (`stage_name` ASC),
	INDEX (`last_log` ASC)
	) ENGINE=MyISAM;";

AND

    $sql = "create table user (
	id MEDIUMINT NOT NULL AUTO_INCREMENT,
	tec_id VARCHAR(15) NOT NULL UNIQUE,
        created DATE,
	agent_id VARCHAR(15),
	type VARCHAR(50),
	approved DATE,
	dob DATE,
	age_create VARCHAR(3),
	first_name VARCHAR(100),
	last_name VARCHAR(100),
	address_01 VARCHAR(150),
	address_02 VARCHAR(150),
	country VARCHAR(50),
	state VARCHAR(50),
	city VARCHAR(50),
	zip CHAR(12),
	referring_user VARCHAR(15),
        referring_code VARCHAR(100),
	terms_accept VARCHAR(3),
	terms_initials VARCHAR(5),
	terms_date DATE,
	rate VARCHAR(5),
	PRIMARY KEY (id),
	INDEX (`tec_id` ASC),
	INDEX (`city` ASC),
	INDEX (`type` ASC)
	) ENGINE=MyISAM;";
[reply]
      poj! Thanks mate SOOOOOOO much it works!!!! EXACTLY what i needed - YOU ROCK!
[reply]

Back to Seekers of Perl Wisdom