update trigger

Question

update trigger

Thomas Wareham

SSC Veteran

Points: 219
More actions
September 11, 2007 at 3:26 am

#197822

Quick question here, I'm doing an update trigger, in the trigger code how do I restrict it from updating a certain column, e.g. say a teacher is updating the details of a "Course" table and he should be able to update all of them except the "Course Name" column.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

You can access the old and new values via the inserted and deleted tables. I assume you also have a PK column, so if you join these two tables on the PK, and check if the two non-modifiable columns are the same, you will know that the user has modified the column or not. If the user modified, you can roll back the transaction.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 2

You could also look at COLUMNS_UPDATED ( )

http://technet.microsoft.com/en-us/library/ms186329.aspx

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 3

That seems rather complex, I think there should be an easier way (if not I guess I will have to try your suggestion). Plus I don't want to rollback transaction, I still want the update to go through but not update the particular "Course Title" column (maybe just print to the results 'that column could not be updated'), but it should update all other columns.

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 4

You could also use UPDATE (restrictedColumn) this returns true or false for a particular column.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

noeld SSC Guru Points: 96590 More actions · Answer 5

That sounds like a CLIENT side issue. If you need to enforce that on the server Use a view to restrict the columns that can be updated and run the update on the view.

* Noel

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

No need for a trigger... You can revoke permissions from individual users by column in a table or view. Lookup REVOKE in Books Online for the full syntax.... but here's a simplified example...

REVOKE UPDATE ON yourtablename (yourcolumnname)

FROM securityaccount_or_loginname

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thomas Wareham SSC Veteran Points: 219 More actions · Answer 7

Hmm, I might need to check the specification, but I don't think this is about users, I'm pretty sure there is just one user, the administrator and technically he has access to everything, but when calling an

UPDATE Course

SET "Course Name" = newcoursename

This should not be allowed but all other column updates should be allowed.