Hi all,
I am writing a Insert trigger but to my dismay it is not able to insert
explicitly into columns of type Timestamp.

Please let me know the work around for this.

regards,
Joy

Joy wrote:
The point of a TIMESTAMP is that it is updated automatically for you.
If you want to insert some particular value then use a different
datatype (BINARY for example).

Dear David,
It would be great if you could provide me with some example.

regards,
Joy

Show quoteHide quote

Joy (J**@discussions.microsoft.com) writes:
Example of what?

Maybe you should clarify why you want to insert an explicitl value into
a timestamp column?

Beware that timestamp is a data type that bears no relation to date and
time. It's an 8-byte binary value which is guaranteed to be unique within
the database, and every time you update a row with a timestamp column,
the column is set to the current timestamp value. The main purpose of
timestamp values is to support optimistic concurrency.

Hi Erland,
Well i want to insert explicit value into timestamp values because i am
writing a process that is similar to replication meaning i have front end and
back end database and the front end has some tables that have columns of type
timestamp and when i run my process the respective rows at both the databases
should have the same value.

shall be waiting for your feedback at the earliest.

thanks,
Joy

Show quoteHide quote

Joy (J**@discussions.microsoft.com) writes:
Why would you want the timestamp value to be same value? Does that serve
any purpose, or is just because it looks nice?

You cannot explicitly set a timestamp value. If you really need to replicate
the timestamp value, you need to change the column on the target side to
binary(8).

Hi,
What if i cannot change the tagret column to binary(8), is there still a way
for explicit insert, because the business proposition is such i cannot alter
the table schema.

regards,
Joy

Show quoteHide quote

Joy (J**@discussions.microsoft.com) writes:
No, there isn't. Again, I ask you to consider if you really need to
duplicate the timestamp value.

You have two options:
1) Don't duplicate the timestamp value.
2) Change the target table.
No matter what your business proposition is.

Hi,
Now i am faced with a situation where i need to update a table that doesn't
have a primary key how do i make sure i have the least number of columns in
where clause.

regards,
Joy

Show quoteHide quote

On Sun, 27 Aug 2006 05:22:02 -0700, Joy
<J**@discussions.microsoft.com> wrote:

If the table does not have a primary key, or a unique constraint or
index, it may not be possible to update a single row.  This
demonstrates at a very practical level why every table needs a PK.

When faced with a table that does not have a PK, the question should
be "Is there a potential PK?"  Ideally a person familiar with the data
will be able to suggest an answer, which will be one or more columns.
That answer can be tested with the following query:

SELECT <list of candidate key columns>, count(*) as Duplication
FROM <table without PK>
GROUP BY <list of candidate key columns>
HAVING COUNT(*) > 1

If this query returns anything the columns will not work as a PK.

Now, even if there are no results there may still be problems.  A PK
can only include columns that do not allow NULL.; the alternative for
that is a UNIQUE contraint or index, which do not have that
restriction.  What can be a much bigger problem is when the data in
the table at the time the test above is run is not representative of
the data that can occur.

Roy Harvey
Beacon Falls, CT

How about using a DATETIME column with a default value of GETDATE() ?

nText,Text,Image field problem for Update/Delete Trigger
Returned first row of a column in a group by
Sub Query Problem
LEFT JOIN... WHERE vs AND ?
Optimizing this crazy setup...