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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Trigger - Updating some columns

Author  Topic 

Meltdown
Starting Member

37 Posts

Posted - 2008-11-22 : 17:26:42
Hi all,

I need to create a trigger that will only allow some of the columns in the table to be updated, can anyone explain to me how to achieve that.

The trigger checks if the locked column is set to True, if it is, 10 of the columns are locked,but 5 of them must still be updatable.

This is what i've got so far:

USE [MyDatabase]
GO
/****** Object: Trigger [dbo].[valdata] Script Date: 11/22/2008 22:23:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Trigger [dbo].[valdata]
ON [dbo].[tblSales]
FOR UPDATE
AS

DECLARE @IsLocked BIT

SELECT @IsLocked = (SELECT Locked FROM Deleted)


If(@IsLocked = 1)
BEGIN
--This is where I want user to update certain columns
END
ELSE
BEGIN
ROLLBACK
RAISERROR('Sorry, you cannot edit this record',14,1)
RETURN
END

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-22 : 17:42:40
Join the inserted rows to the updated rows on the primary key and rollback if any of the "locked" columns have been updated.





CODO ERGO SUM
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-22 : 18:07:29
Sorry Michael, I don't understand what you mean. I don't have any new inserted rows, just updatable ones.

How do I allow some of the columns to accept the update and others to reject the update?

I'm starting to think I need an 'Instead Of' trigger, where I update the row from the INSERTED table and only select/update the columns that are allowed. Do you think that is best/quickest approach?

Regards
Melt
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-22 : 18:42:20
Do you want to prevent updates on columns or cghanges in values?
e.g. if a user is allowed to update co1 but not col2
would

update tbl
set col1 = '1', col2 = col2

be allowed?

If not you can use the updated flag otherwise you will need to compare inserted and deleted.


==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-22 : 19:14:51
Hi, I want stop updates on specific columns in a row but allow other columns to be updated.

So, UserA can update Col1,Col2,Col3 but can't update Col4,Col5 and so on.

I can't just set table permissions as the columns the user is allowed to update is based on the role they're in and the value of a 'locked' column in the row.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 01:52:31
quote:
Originally posted by Meltdown

Hi, I want stop updates on specific columns in a row but allow other columns to be updated.

So, UserA can update Col1,Col2,Col3 but can't update Col4,Col5 and so on.

I can't just set table permissions as the columns the user is allowed to update is based on the role they're in and the value of a 'locked' column in the row.




you mean column is locked only for few users? in that case, i think it would be better to create a view with only unprotected columns and give update rights to view for those users. for base table give select permission alone for them. for others give all permissions to base table.
Go to Top of Page

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-23 : 06:26:38
You can give column level permission in Sql server as well.
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-23 : 13:26:36
Hi ,

Thanks for the replies, but none of them will work. View permissions and column level permissions don't allow you to check the value of the 'locked' column in my row. My permission set has to be based on two criteria.

1. The Users Role
2. The value of the 'locked' column in the row.

Can I repost what I said above:
I'm starting to think I need an 'Instead Of' trigger, where I update the row from the INSERTED table and only select/update the columns that are allowed. Do you think that is best/quickest approach?..or is it even possible?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-23 : 13:32:06
its possible...but definitely it will have an impact on performance. Where will this locked info be held? in same table or in some other table?
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-11-23 : 17:39:56
I think it would be simpler to have your application check before updating the table, instead of catching it as an error in a triggger.



CODO ERGO SUM
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2008-11-24 : 01:42:31
quote:
Originally posted by Meltdown

Hi, I want stop updates on specific columns in a row but allow other columns to be updated.

So, UserA can update Col1,Col2,Col3 but can't update Col4,Col5 and so on.

I can't just set table permissions as the columns the user is allowed to update is based on the role they're in and the value of a 'locked' column in the row.






Doesn't answer the question.
By update do you mean changing the value or executing an update statement including the column?



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-24 : 13:35:56
quote:
Originally posted by visakh16

its possible...but definitely it will have an impact on performance. Where will this locked info be held? in same table or in some other table?



Hi, the row that is being updated has a locked column (a bit value), depending on that value some of all of the columnns can be updated.
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-24 : 13:37:15
quote:
Originally posted by nr

quote:
Originally posted by Meltdown

Hi, I want stop updates on specific columns in a row but allow other columns to be updated.

So, UserA can update Col1,Col2,Col3 but can't update Col4,Col5 and so on.

I can't just set table permissions as the columns the user is allowed to update is based on the role they're in and the value of a 'locked' column in the row.






Doesn't answer the question.
By update do you mean changing the value or executing an update statement including the column?



==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.



Sorry I'm not being clear, I mean changing the value in some of the columns.
Go to Top of Page

Meltdown
Starting Member

37 Posts

Posted - 2008-11-24 : 13:40:18
quote:
Originally posted by Michael Valentine Jones

I think it would be simpler to have your application check before updating the table, instead of catching it as an error in a triggger.



CODO ERGO SUM



The problem with the application check is that every form I make then has to have a load of permission code, where if I solve the problem at the table level my permission problem is sorted for future forms that might access data in the table.
Go to Top of Page
   

- Advertisement -