Automatically update datetime field in a database table.

hi e'body:

I have some database tables, and each one of them have a creation_date and modified_date in them. I was trying to figure out a way where when a row in one of these tables is changed using Enterprise Manager (Database -> Tables -> select table -> right click -> select all rows -> change a field in a row inside a table), is there a way apart from triggers, such that the "modified_date" column for that row get changed to 'getdate()' (rather picks up the current datetime).

thanks in advance.Here is the generic trigger I use to record who modified a record and when:
CREATE TRIGGER TR_[TABLENAME]_U ON dbo.[TABLENAME]
FOR UPDATE
AS
set nocount on
update [TABLENAME]
set Modified = getdate(),
Modifier = isnull(inserted.Modifier, (convert(nvarchar(50),suser_sname())))
from [TABLENAME]
inner join Inserted on TABLENAME.PKey = Inserted.PKey
set nocount offNote that this trigger is for update only. For inserts, you should have default values defined on the table.

Automatic updating of datetime field

I need to automatically update a datetime field for a record to the current time whenever the record is updated.

create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go

One option would be to use an instead of update trigger.

create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go

update t set value='dick' where id=1 and name='fred'
go

Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.

Any ideas?...also, you don't know what database server it is going to be running on, so it has to be platform-independent.

...and it needs to be fully compatible with the Mayan calendar as well.

...oh yeah, and the final code must be a palindrome that reads the same way forwards as backwards! Yeah, that's it! What a kick-ass application design! Whooooo-eeeeeeee!sql

Auto-generating a DateTime in SQL Server Express 2005

Is it possible to have a DateTime field in a row automatically generated when a row is created?

ie:

INSERT INTO [TableName] ([DateTimeStamp], [UserId], [Title], [Explanation]) VALUES (DateTime.Now(), @.UserId, @.Title, @.Explanation)

or something like that?

You can set up a default value of GetDate() to the column in the design view of the table.

|||

Thanks! Exactly the solution I was looking for.

Auto join in SQL Query

I've got the following table :
CREATE TABLE M6_CHECK
(
ID_QUOTE VARCHAR(20),
ID_ITEM VARCHAR(20),
BEGIN_DATE DATETIME,
END_DATE DATETIME
)
This table contains the following data :
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I1','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I2','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q1','I3','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I1','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q2','I4','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I2','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q3','I5','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I3','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q4','I6','01/01/2000','12/31/2000');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q5','I4','01/01/2001','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q6','I5','01/12/2000','12/31/2001');
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q7','I6','01/31/2001','12/31/2001')
I am trying to find a SQL query which would return the following recordset :
ID_ITEM ID_QUOTE_1 END_DATE_1 ID_QUOTE_2 BEGIN_DATE_2
----
I5 Q3 12/31/2000 Q6 01/12/2000
I6 Q4 12/31/2000 Q7 01/31/2001
Let me explain :
The SQL Query should return every ID_ITEM for which 2 consecutives quotes
don't verify the following rule :
end date previous quote = begin date next quote - 1 day
It looks like I should make an auto join in the query but I couldn't make it
:(
Thanks in advance for your help,
AlexSELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t1.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_QUOTE,t1.ID_ITEM,t1.BEGIN_DATE|||typo..
t1.BEGIN_DATE as BEGIN_DATE_2
should be
t2.BEGIN_DATE as BEGIN_DATE_2|||This doesn't show up in the test data, but
more correctly I think it should be this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.BEGIN_DATE<t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
AND t2.BEGIN_DATE=(SELECT MIN(t3.BEGIN_DATE)
FROM M6_CHECK t3
WHERE t3.ID_ITEM=t1.ID_ITEM
AND
t3.BEGIN_DATE>t1.BEGIN_DATE)
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE|||It's almost the good result. The problem is that if you add the following
record
insert into M6_CHECK(ID_QUOTE, ID_ITEM, BEGIN_DATE, END_DATE)
VALUES('Q9','I3','01/01/2001','12/31/2001');
This record should appear in the result, and with your query it does not :(
Damn it !!
Thanks a lot for your help !!
Alex|||What result are you expecting? Q1 -> Q9 or Q9 ->Q1 or both?
Try this
SELECT t1.ID_ITEM,
t1.ID_QUOTE as ID_QUOTE_1,
t1.END_DATE as END_DATE_1,
t2.ID_QUOTE as ID_QUOTE_2,
t2.BEGIN_DATE as BEGIN_DATE_2
FROM M6_CHECK t1
INNER JOIN M6_CHECK t2 ON t1.ID_ITEM=t2.ID_ITEM
AND t1.ID_QUOTE<t2.ID_QUOTE
AND t1.BEGIN_DATE<=t2.BEGIN_DATE
AND DATEADD(day,1,t1.END_DATE)<>t2.BEGIN_DATE
ORDER BY t1.ID_ITEM,t1.ID_QUOTE,t1.BEGIN_DATE