restricting permission to a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

restricting permission to a column

Author

Topic

_sqllearner
Starting Member

9 Posts

Posted - 2007-03-29 : 16:16:43

I have a table with UpdateDate field and I want this field to be populated when a new record is inserted or an existing record is updated using the getdate().
But I dont want the users who insert or update records in this table to have control over this field. Meaning the users should not be able to populate or update this field.
At the same time when the user inserts or updates a record this field should be populated automatically.

Can this be done. Please let me know.

Thanks
Raj

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-29 : 16:21:08

For the insert you just need to add getdate() as the default for the column, in order to have it updated again whenever the row is updated you'll need to add a for update trigger on the table.

_sqllearner
Starting Member

9 Posts

Posted - 2007-03-29 : 17:03:21

Thanks for your reply.
I would like to update only the particular record that gets updated and not all the records in the table.
Trigger code below updates all the rows, even if a single row in the table is update. How do I restrict it to only the particular row that gets updated

CREATE TRIGGER [testtrigger] ON [dbo].[Table1]
FOR UPDATE
AS
update table1
set updatedate=getdate()

Go

Thanks
Raj

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-29 : 17:53:18

In the trigger, use the inserted table to get only those rows that were updated

CREATE TRIGGER [testtrigger] ON [dbo].[Table1] 
FOR UPDATE 
AS
update table1
set updatedate=getdate()
from table1
inner join inserted on inserted.PKCol = table1.PKCol

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources