Prevent update trigger

Question

Prevent update trigger

Nqobilemoyo

Old Hand

Points: 362
More actions
July 9, 2018 at 10:12 am

#354177

I have a database and i would like to create a trigger which will not allow a user to update a customers name and will provide a message explaining the block
I have been able to successfully create the message trigger however need help creating the update prevention trigger.
Find below my message trigger:
[Create trigger [dbo].[CustomerErrorMessage]
on [dbo].[tblCustomer]
after update
as
begin
print 'Sorry, unable to update customer name'
end ]
Please find below the database sample:

Viewing 5 posts - 1 through 4 (of 4 total)

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Nqobilemoyo - Monday, July 9, 2018 10:12 AM
I have a database and i would like to create a trigger which will not allow a user to update a customers name and will provide a message explaining the block
I have been able to successfully create the message trigger however need help creating the update prevention trigger.
Find below my message trigger:
[Create trigger [dbo].[CustomerErrorMessage]
on [dbo].[tblCustomer]
after update
as
begin
print 'Sorry, unable to update customer name'
end ]
Please find below the database sample:

Use ROLLBACK TRANSACTION.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 2

Nqobilemoyo - Monday, July 9, 2018 10:12 AM
I have a database and i would like to create a trigger which will not allow a user to update a customers name and will provide a message explaining the block
I have been able to successfully create the message trigger however need help creating the update prevention trigger.
Find below my message trigger:
[Create trigger [dbo].[CustomerErrorMessage]
on [dbo].[tblCustomer]
after update
as
begin
print 'Sorry, unable to update customer name'
end ]
Please find below the database sample:

Don't use an AFTER trigger for this. Use an INSTEAD OF trigger, instead. (pun intended).

--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)

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 3

You can use the UPDATE() or COLUMNS_UPDATED() functions to check if the column has changed. Or use a query to compare inserted.customername with deleted.customername.

https://docs.microsoft.com/en-us/sql/t-sql/functions/columns-updated-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/functions/update-trigger-functions-transact-sql?view=sql-server-2017

ScottPletcher SSC Guru Points: 101099 More actions · Answer 4

If the app/person/whatever doing the UPDATE is not a sysadmin, (or perhaps a dbo, not sure on that one), maybe you can just DENY UPDATE on that column:


DENY UPDATE ON dbo.tblCustomer ( customer_name ) TO public|<user[,...]>;

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".