Posted by Ted on 10/04/85 11:54

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? (Yes, I know 'timestamp' is
deprecated in MS SQL and that I should use datetime instead, and in
fact have already done so.)

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.

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.
This is in reference to a pair of scalar functions I am using which
need to manipulate date and time values. For the purpose of
clarification, is this documentation refering to all date/time data
types, or only the deprecated timestamp type? As examples, consider
one function that needs to return the most recent date in a date column
in a specific table, or another function that computes a date from a
date and an offset (e.g. if called with the value returned by the first
function as the first argument and '-7' as the second, returns the date
of the day that is a week earlier than that date). These two functions
are frequently used in the SQL code I'm trying to port and I really
don't want to complicate so many of those statements if I don't have
to.

Thanks

Ted

[Back to original message]