MySQL Triggers

Not tried using these before. I want to add the Unix time stamp when I create a record so was trying this:-

DELIMITER $$
CREATE
TRIGGER `cl35-deal`.`EventDate` BEFORE INSERT
ON `cl35-deal`.`globalevents`
FOR EACH ROW BEGIN
SET NEW.GEDate = UNIX_TIMESTAMP(NOW());
END$$
DELIMITER ;

Open in new window


It compiles fine but doesn't add the time stamp. What am I missing?
LVL 1
cescentmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jimyXCommented:
> It compiles fine but doesn't add the time stamp. What am I missing?

Set the datatype of GEDate to BigInt. From the name you gave I suspect you set it to Date, which is not going to work.
The Unix Time stamp looks like: 1427790103
Chris StanyonWebDevCommented:
You don't need a trigger for that - just set your column to timestamp with a default value of CURRENT_TIMESTAMP. When you create a record, don't insert a value into that column and it will automatically be timestamped.

Here's the column def you need:

TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
cescentmanAuthor Commented:
JimyX

GEDate was int(15) I've changed it to bigint(20) it makes no difference.

Chris_Stanyon

Thanks for the suggestion, I was aware I can set "DEFAULT CURRENT_TIMESTAMP ". As I say above I want to insert the Unix time stamp.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Chris StanyonWebDevCommented:
A timestamp is a timestamp. If you set the default as I said, then you can just pull it back out as a timestamp when you need to.

SELECT UNIX_TIMESTAMP(GEDate) FROM yourTable
cescentmanAuthor Commented:
Chris_Stanyon

Thanks for your comment but my primary concern is to understand why the trigger is not working. Do you have any idea what I'm doing wrong?
jimyXCommented:
Cescentman,
You are not doing anything wrong (looking and talking about the query in-hand). I copy-pasted your trigger and it works OK. It could be a permission issue.

What version of MySQL you are using?
cescentmanAuthor Commented:
jimyX

I'm using 5.5.33
Chris StanyonWebDevCommented:
Your trigger works OK, but only if you have a suitable data type for the GEDate column (INT, VARCHAR etc). Date won't work!
cescentmanAuthor Commented:
Chris_Stanyon

Thanks, you've hit the nail on the head. So now the question is why mine isn't working.
jimyXCommented:
Can't thing of anything other than permissions. Ensure the user has Trigger privilege to be able to execute triggers.
Or you could try that with root account.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Chris StanyonWebDevCommented:
You still haven't told us what data type the GEDate column is. If you want to store the timestamp, it has to be INT or VARCHAR. Make sure your column is set to one of those data types.
cescentmanAuthor Commented:
Chris_Stanyon

See my response to JimyX above:-

"GEDate was int(15) I've changed it to bigint(20) it makes no difference."
cescentmanAuthor Commented:
jimyX

This isn't a DB I have root access to but the DB manager set the following:-

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, TRIGGER ON `cl35-deal`.* TO '*username*'@'%' WITH GRANT OPTION
cescentmanAuthor Commented:
I have just heard back from the DB manager as I posted them too. It is now working heaven knows what they did. Thanks for the help
Chris StanyonWebDevCommented:
My bad - didn't see that comment.

Not really sure what's going on. If you have the permissions and your data type is correct, then it should work as expected.

Just as a quick test, add a varchar column and then create a trigger that just sets it to some static text. Delete your EventDate trigger first. Should at least tell you if your triggers are being fired.

DROP TRIGGER IF EXISTS `cl35-deal`.`EventDate`;

DELIMITER $$
CREATE TRIGGER `cl35-deal`.`testTrigger` BEFORE INSERT ON `cl35-deal`.`globalevents`
FOR EACH ROW
BEGIN
	SET NEW.yourCol = 'test';
END$$
DELIMITER;

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.