December 12, 2009 at 10:05 am
My SQL as follow,
declare @tEmployee table
(
TrnxID int primary key identity(1,1),
Nme varchar(100),
RowID rowversion
)
insert into @tEmployee(Nme) values('Mike Tyson')
insert into @tEmployee(Nme) values('Hollyfield')
It's possible, the RowID value equal to 0x0000000000000000 after insert or update statement?
So far, I don't believe it can be 0x0000000000000000. Why? I dont know. Hopefully, someone can give an answer
December 13, 2009 at 1:45 am
I hope this little test script will provide some insight on the timestamp data type.
/****** :) best regards ALZDBA ******/
set nocount on
go
/*
@@DBTS returns the last-used timestamp value of the current database.
A new timestamp value is generated when a row with a timestamp column is inserted or updated.
*/
select @@DBTS
go
drop table dbo.t_test
go
print 'tabel dropped'
go
CREATE TABLE dbo.T_Test (
Sleutel int IDENTITY (1, 1) NOT NULL ,
Ms_Ts timestamp NOT NULL ,
Ms_Datetime datetime NOT NULL ,
Ms_Datetime_Last_Used datetimeNOT NULL ,
Ms_Char char (10) NOT NULL
)
GO
print 'Table reated'
go
ALTER TABLE dbo.T_Test WITH NOCHECK ADD
CONSTRAINT DF_T_Test_Ms_Datetime DEFAULT (getdate()) FOR Ms_Datetime,
CONSTRAINT DF_T_Test_Ms_Datetime_Last_Used DEFAULT (getdate()) FOR Ms_Datetime_Last_Used,
CONSTRAINT PK_T_Test PRIMARY KEY NONCLUSTERED
(
Sleutel
)
GO
print 'Constraints added'
go
--drop trigger TrU_Ms_Datetime_Last_Used
--go
CREATE TRIGGER TrU_Ms_Datetime_Last_Used ON T_Test
FOR UPDATE
AS
if not UPDATE(Ms_Datetime_Last_Used)
begin
update T_Test set Ms_Datetime_Last_Used = CURRENT_TIMESTAMP where sleutel = ( select sleutel from deleted )
end
go
print 'Trigger added'
insert into t_test (Ms_Char) values('a')
go
insert into t_test (Ms_Char) values('b')
go
Print 'Rows inserted'
go
select * from t_test
go
Print 'Update starts here...'
go
update t_test set ms_char = 'c' where sleutel = 1
go
Print 'Sleutel 1 updated ...'
go
select * from t_test
go
select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel
go
update t_test set ms_char = 'D' where sleutel = 2
go
Print 'Sleutel 2 updated ...'
go
select * from t_test
go
select sleutel, ms_ts, cast(ms_ts as datetime) as Cast_ms_ts_datetime
, CONVERT (datetime, ms_ts) as Convert_ms_ts_datetime
from t_test
order by sleutel
go
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply