CODEWITHSUNDEEP
javamysqltriggersh2
Seve B.
Seve B.

Reputation: 55

MySQL update trigger error; setting a value in an updated record

I am trying to make a trigger that sets the column last_modified to CURRENT_TIMESTAMP after an update is successfully performed on any row. I am using the h2 library in java.

Here is my table declaration

CREATE TABLE Professors
(utorID VARCHAR(30) NOT NULL,
FName VARCHAR(20),
LName VARCHAR(20), 
Email VARCHAR(50),
UTEmail VARCHAR(50), 
Birthdate VARCHAR(10),
OfficeBC VARCHAR(2),
OfficeRM VARCHAR(6),
Department VARCHAR(20),
Status VARCHAR(50),
Fellowship VARCHAR(20),
OfficeStat VARCHAR(15),
PhoneNum VARCHAR(10),
HomeAddr VARCHAR(50),
HomePhoneNum VARCHAR(10),
Notes VARCHAR(100),
last_modified TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
lastPerson VARCHAR(50),
PRIMARY KEY (utorID),
CONSTRAINT uc_ProfNames UNIQUE(FName, LName)) ENGINE=InnoDB;

And here is my trigger ATTEMPT ONE:

CREATE TRIGGER Update_Time_Profs
AFTER UPDATE ON
Professors
FOR EACH ROW
BEGIN
 UPDATE Professors
 SET last_modified=CURRENT_TIMESTAMP
 WHERE utorID=OLD.utorID;
END;

And here is my trigger ATTEMPT TWO, since I read that it is not possible to update within an update trigger (since this may cause an infinite loop), even though I have used the above in SQLite and it worked properly:

           CREATE TRIGGER Update_Time_Profs 
           BEFORE UPDATE ON 
           Professors
           FOR EACH ROW 
           BEGIN 
            SET NEW.last_modified=CURRENT_TIMESTAMP; 
           END;

but neither are working :(

Thanks in advance for your help

Upvotes: 0

Views: 78

Answers (1)

Tom Mac
Tom Mac

Reputation: 9853

One option would be to avoid triggers all together and change the column definition of the last_modified column to this:

`last_modified` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Should do the trick.....

Upvotes: 1

Related Questions