Posted by David Portas on 10/15/84 11:54

Ted wrote:
> 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


1) You can use an UPDATE trigger to simulate the same functionality.
Usually though it is better to use stored procedures to perform all
your data access. That way you can easily include the timestamp as part
of your update procs.

2) You don't need separate scripts for each View / Function. You do
need separate batches. A batch is separated using the GO keyword in
Query Analyzer.

3) Don't confuse TIMESTAMP with DATETIME. They are not at all the same!
You are referring to DATETIME values, which ARE permitted as parameters
and return values in functions. BTW, you don't need to write a function
to do date arithmetic - it already exists as a built-in function:
DATEADD().

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]