|
|
Posted by Erland Sommarskog on 10/15/92 11:54
Ted (r.ted.byers@rogers.com) writes:
> 1) In several tables, in my MySQL version, I created columns using
> something like the following:
>
> `ab_timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update
> CURRENT_TIMESTAMP,
>
> This allowed me to ensure that when a record is either added or edited,
> the value in the field is set to the current date and time. I.E.,
> ab_timestamp is given the current date and time when a record is
> created, and then it is updated to the date and time at which the
> record is updated. I learned the hard way that MS SQL does not like
> "on update CURRENT_TIMESTAMP". So, it looks like MS SQL will allow me
> to initialize ab_timestamp to the current date and time, but not
> automatically update it to the date and time at which the record is
> updated. I have plenty of code to port that depends on the behaviour
> supported by MySQL. DO I have to modify all that code, or is there a
> way to get MS SQL to provide it?
You will have to modify all that code. To have to be portable is indeed
painful, and a good start is to pay attention to what is in ANSI standards,
what is commonly supported. And not to the least to what are extensions
added by a certain vendor. My knowledge of ANSI and other engines are
poor (since I live in a sheltered world where I only need to support
SQL Server), but I would suspect that the ON UPDATE clause for the
default constraint is properitary to MySQL.
The way to do this in SQL Server is to use a trigger. Not that triggers
usually are very portable...
You can also modify the UPDATE statements so that they read:
UPDATE tbl
SET ...,
ab_timestamp = DEFAULT,
WHERE ...
I believe this syntax is portable.
> (Yes, I know 'timestamp' is deprecated in MS SQL and that I should use
> datetime instead, and in fact have already done so.)
timestamp is not deprecated in SQL Server, but it's a completely different
data type, which is used to implement optimistic locking. A timestamp
is an 8-bit value is updated each time the row is updated and it's
unique within the database. Further more timestamp value are monotonically
increasing. But there is no correlation with time. Timestamp is
proprietary to SQL Server, so you should probably stay away from it
entirely.
> 2) I began with a single SQL script that creates all the tables, views,
> functions and triggers the database needs. On trying to get MS SQL to
> accept it, I encountered a number of error messages saying that CREATE
> FUNCTION and CREATE VIEW need to be the first statement in a script.
> Why? I know I can work around this odd constraint by putting each
> function and view (and IIRC trigger) into its own script, but that
> seems like a make work effort imposed for some unknown reason by MS
> SQL, unless there is another way to get around it.
Not a separate script, but a separate batch. Batches are separated with
"go" in all query tools. The graphic tools permit you to specify a
different batch separator.
Why? Because else it would be difficult to tell where a procedure ends.
Say that you have:
CREATE PROCEDURE .... AS
....
CREATE TABLE ....
Is that CREATE TABLE part of the procedure or not? (Yes, if you have
BEGIN END it's clear. But of legacy BEGIN END is not required in
stored procedures.)
> 3) I see, in the documentation for CREATE FUNCTION, functions are not
> allowed to use a timestamp for either a parameter or a return value.
As noted above, there is little reason for your to use the timestamp
data type.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|