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 ONGOSET QUOTED_IDENTIFIER ONGOCREATE Trigger [dbo].[valdata]ON [dbo].[tblSales]FOR UPDATEASDECLARE @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 |
|
|
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?RegardsMelt |
|
|
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 wouldupdate tblset col1 = '1', col2 = col2be 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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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 Role2. 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? |
|
|
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? |
|
|
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 |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
|