CODEWITHSUNDEEP
mysqlperl
Okazaki Naoki
Okazaki Naoki

Reputation: 47

MySQL Trigger after insert and add timestamp when data changed

This is my perl sample code and what i needed is add a timestamp into columns when data changed(updated).

use strict;
use DBI;
use POSIX qw(strftime);

my $datestring = strftime "%Y-%m-%d %H:%M:%S",localtime;
my $dbh = DBI->("dbi:mysql:dbname=perldb","root","111111",{RaiseError=>1})
    or die DBI::errstr();

my $mac1='mac1';
my $ip1='ip1';
my $mac2='mac2';
my $ip2='ip2';

$dbh->do("DROP TABLE IF EXISTS Test1");
$dbh->do("CREATE TABLE Test1(Id INT PRIMARY KEY AUTO_INCREMENT,IP TEXT,MAC TEXT,TIME DATETIME,PrevTime DATETIME) ENGINE=InnoDB");
$dbh->do("CREATE Trigger trigger1 AFTER INSERT ON Test1
        FOR EACH ROW
        BEGIN
        IF OLD.MAC!=NEW.MAC
        THEN SET NEW.TIME='$datestring';
        END IF;
        END;")

$dbh->do("INSERT INTO Test1(IP,MAC) VALUES('$ip1','$mac1') ON DUPLICATE KEY UPDATE IP=VALUE(IP)");
$dbh->do("INSERT INTO Test1(IP,MAC) VALUES('$ip2','$mac2') ON DUPLICATE KEY UPDATE IP=VALUE(IP)");

$dbh->disconnect();


The goal I want to reach is run this code once then modify $mac1 to 'mac3'.
So that it will trigger SET timestamp because the data was changed.
Also SET old one timestamp to PrevTIME, but OLD for INSERT.

Result I need maybe like this: +----+------+------+---------------------+----------+ | Id | IP | MAC | TIME | PrevTime | +----+------+------+---------------------+----------+ | 1 | ip1 | mac1 | 2015-03-03 12:34:56 | NULL | | 2 | ip2 | mac2 | 2015-03-03 12:34:56 | NULL | +----+------+------+---------------------+----------+

Then +----+------+------+---------------------+---------------------+ | Id | IP | MAC | TIME | PrevTime | +----+------+------+---------------------+---------------------+ | 1 | ip1 | mac3 | 2015-03-03 12:40:00 | 2015-03-03 12:34:56 | | 2 | ip2 | mac2 | 2015-03-03 12:34:56 | NULL | +----+------+------+---------------------+---------------------+

Anyidea? Please help me figure out,THANK YOU.

-------------------------------SOLUTION---------------------------------------

use strict;
use DBI;
use POSIX qw(strftime);

my $datestring = strftime "%Y-%m-%d %H:%M:%S",localtime;
my $dbh = DBI->("dbi:mysql:dbname=perldb","root","111111",{RaiseError=>1})
    or die DBI::errstr();

my $mac1='mac1';
my $ip1='ip1';
my $mac2='mac2';
my $ip2='ip2';

$dbh->do("CREATE TABLE IF NOT EXISTS Test1(Id INT PRIMARY KEY AUTO_INCREMENT,IP TEXT,MAC TEXT,TIME DATETIME,PrevTime DATETIME,UNIQUE(IP)) ENGINE=InnoDB");
$dbh->do("CREATE Trigger trigger1 BEFORE INSERT ON Test1
        FOR EACH ROW
        BEGIN
        SET NEW.TIME='$datestring';
        END;")
$dbh->do("CREATE Trigger trigger2 BEFORE UPDATE ON Test1
        FOR EACH ROW
        BEGIN
        IF OLD.MAC <> NEW.MAC
        THEN
        SET NEW.PrevTIME=OLD.TIME;
        SET NEW.TIME='$datestring';
        END IF;
        END;")

$dbh->do("INSERT INTO Test1(IP,MAC) VALUES('$ip1','$mac1') ON DUPLICATE KEY UPDATE IP=VALUE(MAC)");
$dbh->do("INSERT INTO Test1(IP,MAC) VALUES('$ip2','$mac2') ON DUPLICATE KEY UPDATE IP=VALUE(MAC)");

$dbh->disconnect();

Upvotes: 0

Views: 2166

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You actually need 2 triggers one before insert and one before update. The before insert will set the TIME to now(), while the before update will check the old and new values of mac and set the PrevTime to old TIME and TIME to now()

Here are the triggers, you can have them in your code

delimiter //
create trigger trigger1 before insert on Test1
for each row
begin
  set new.TIME = now();
end ; //


delimiter //
create trigger trigger2 before update on Test1
for each row
begin
  if old.MAC <> new.MAC then
    set new.PrevTime = old.TIME ;
    set new.TIME = now();
  end if;
end ;//

Upvotes: 3

Ravinder Reddy
Ravinder Reddy

Reputation: 24002

Triggers are event specific.

You are using an INSERT and hence AFTER or BEFORE Trigger works defined for INSERT events.

You actually need a BEFORE UPDATE trigger. Add a similar trigger for BEFORE TRIGGER

Example: 

CREATE TRIGGER bu_on_Test BEFORE UPDATE ON Test1
FOR EACH ROW
BEGIN
   IF OLD.MAC <> NEW.MAC THEN
       SET NEW.TIME='$datestring';
   END IF;
END;

But note that Triggers won't take dynamic input parameters like '$datestring'.
If such a value is set in your scripting language, that is for lifetime of trigger definition and is not going to change during its execution.

Upvotes: 0

Related Questions