March 5, 2012 at 1:05 pm
We are working on moving one of our production databases from mysql to SQL Server. We are running SQL 2008 Enterprise edition, if that makes a difference with the answer provided.
We did a dump from mysql which provides the CREATE statements. One of the tables is getting hung up on a column where mysql apparently has an ON UPDATE property:
CREATE TABLE accountlines (
...,
timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
...)
I'm assuming this is saying that when the row is updated, the timestamp is updated to the SQL equivalent GETDATE(). I can't seem to find if this is an option or not (looking like not) so I'm thinking probably a trigger to make this happen? Since I have not written any triggers to date, anyone care to take a stab at what the solution might look like?
Thanks in advance!
March 6, 2012 at 1:49 am
The ON UPDATE clause is a proprietary MySQL syntax to automatically update a column to the current timestamp whenever the row is updated.
You can do that in SQLServer with a trigger:
CREATE TABLE accountLines (
id int PRIMARY KEY CLUSTERED,
someField int,
ts datetime DEFAULT GETDATE()
);
GO
CREATE TRIGGER accountlines_setCurrentTime ON accountlines
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
UPDATE al
SET ts = GETDATE()
FROM accountLines AS al
INNER JOIN inserted AS i
ON al.id = i.id;
END
GO
INSERT INTO accountLines(id,someField) VALUES(1,1); -- ts gets the default
INSERT INTO accountLines(id,someField) VALUES(2,2); -- ts gets the default
SELECT * FROM accountLines;
-- ts is modified by te trigger
UPDATE accountLines
SET someField = 5
WHERE id = 2;
SELECT * FROM accountLines;
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply