Can you automatically update a field in a table every time a record is updated? I want to write to the record as a user update. Currently in every update statement that the application sends to the server, I include the user name and datetime stamp. I want to automate this operation. I tried it by trigger, but the DELETE and INSERT (virtual table) don't permit updating the field. What do you suggest?
Set a default value so the column is automatically populated when you add data to the table:
CREATE table mytable
( c1 int NOT NULL,
...
username sysname default user_name(),
when_modified datetime default getdate()
)
I tried to update a record with the default value, but the default value is valid for inserts only and not an explicit value for the column. For example, the server automatically inserts the default values for username and when_modified. If I want to automatically set the user and the datetime, I can't use the default statement because the field is already set by a previous insert statement, and the default doesn't work because the data field value is already set. Which user updated and inserted into a row, and when was the action performed? For example:
CREATE TABLE TBRAGMAX (
szMU varchar(5) NOT NULL,
szInsUser sysname default user_name(),
dtIns datetime default getDate(),
szModUser sysname default user_name(),
dtMod datetime default getDate(),
CONSTRAINT XPKTBRAGMAX
PRIMARY KEY (szMU, szCodRag, iYear, iMonth)
ON SEG_01
)
go
Prev. page
[1]
2
next page 
