Make a column immutable, once set it sticks

Question

Post reply

Make a column immutable, once set it sticks

terry999

SSCertifiable

Points: 5033
More actions
October 5, 2015 at 11:15 am

#301188

Hi
I have two columns which once inserted I don't want to allow to change.
I can only think of a trigger.
I have an existing trigger for updates which sets the edited date and copies the old record to an audit table
Is it safe to put a roll back into the existing trigger?
ALTER TRIGGER [dbo].[trgCustomer_upd]
ON [dbo].[Customer]
FOR update
AS
if @@rowcount = 0
RETURN
IF UPDATE(SUPPID)
BEGIN
ROLLBACK TRAN
RAISERROR ('Cannot update SUPPID',16,1)
RETURN
END
IF UPDATE(MANID)
BEGIN
ROLLBACK TRAN
RAISERROR ('Cannot update MANID',16,1)
RETURN
END
declare @date datetime
set @date = getdate()
UPDATE tblCustomer
SET dtEdited = @date
FROM inserted Ins
WHERE tblCustomer.CustID = ins.CustID
INSERT INTO AuditDB..tblCustomer
SELECT ... FROM deleted
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

djj SSCoach Points: 18831 More actions · Answer 1

What about an instead of trigger?

Can the row be deleted?

What about setting the permissions so insert is okay but update is not?

ScottPletcher SSC Guru Points: 100946 More actions · Answer 2

You need to be more selective than that. Any reference to the column will turn the "UPDATE flag" on, even if the value is the same. Maybe something like this:

ALTER TRIGGER [dbo].[trgCustomer_upd]

ON [dbo].[Customer]

FOR update

AS

SET NOCOUNT ON;

if @@rowcount = 0

RETURN

DECLARE @SUPPID_changed bit

DECLARE @MANID_changed bit

SELECT

@SUPPID_changed = MAX(CASE WHEN ISNULL(i.SUPPID, '-999') <> ISNULL(o.SUPPID, '-999') THEN 1 ELSE 0 END),

@MANID_changed = MAX(CASE WHEN ISNULL(i.MANID, '-999') <> ISNULL(o.MANID, '-999') THEN 1 ELSE 0 END)

FROM inserted i

INNER JOIN deleted d ON d.CustID = i.CustID

IF @SUPPID_changed = 1

OR @MANID_changed = 1

BEGIN

DECLARE @raiserror_msg nvarchar(1024)

SET @raiserror_msg = 'Cannot update ' +

CASE WHEN @SUPPID_changed = 1 THEN 'SUPPID' ELSE '' END +

CASE WHEN @MANID_changed = 1

THEN CASE WHEN @SUPPID_changed = 1 THEN ' nor ' ELSE '' END + 'MANID'

ELSE '' END + '.'

RAISERROR (@raiserror_msg,16,1)

ROLLBACK TRAN

RETURN

END

declare @date datetime

set @date = getdate()

UPDATE tblCustomer

SET dtEdited = @date

FROM inserted Ins

WHERE tblCustomer.CustID = ins.CustID

INSERT INTO AuditDB..tblCustomer

SELECT ... FROM deleted

GO --end of trigger

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

mister.magoo SSC-Forever Points: 47068 More actions · Answer 3

It gets even more problematic if CustID can be/is updated, then Scott's trigger won't work either, as the join between INSERTED and DELETED will not return the rows for the check on the other columns.

Of course, if CustID won't change in your case (and it shouldn't in most cases, but sometimes there can be a business case for one customer "taking on" another customer's orders), then that's not an issue.

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

terry999 SSCertifiable Points: 5033 More actions · Answer 4

Thanks for your reply

update tblcustomer

set Supid = Supid

I don't mind if this fails.

As long as the supid and manid are not part of the SET clause of an update I should be ok?

ScottPletcher SSC Guru Points: 100946 More actions · Answer 5

terry999 (10/6/2015)
Thanks for your reply
update tblcustomer
set Supid = Supid
I don't mind if this fails.
As long as the supid and manid are not part of the SET clause of an update I should be ok?

I think so, that in that case you could use IF UPDATE().

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

terry999 SSCertifiable Points: 5033 More actions · Answer 6

Thanks Scott

I've tested the app and the trigger works ok all edits are completed. The app stops these fields being edited anyway. The trigger has stopped these fields being updated in ad-hoc queries.

terry999 SSCertifiable Points: 5033 More actions · Answer 7

djj

Thanks for the idea. I tried and tested the trigger that was my fall-back scenario.

Taking your idea of permissions. I added users to a role and denied update permission on these 2 fields. N.B. I also had to do it to an updatable view based on this table I thought it would inherit the permissions from the underlying table it didn't. Makes sense though you don't have to give permissions on tables if you are using views.

Eric M Russell SSC Guru Points: 125521 More actions · Answer 8

I only use triggers as a last resort, and this isn't one of them. One technique you can use is to add a column containing a CHECKSUM() of whatever columns should not be updated, and then have a check constraint enforce a condition that the checksum value of these non-updatable columns should be equal to the value of the checksum column.

For example:

create table Customer

(

CustomerID int not null primary key,

CustomerName varchar(80) not null,

CustomerPhone varchar(80) not null,

SalesLeadID int not null,

CustomerCheckSum int not null,

constraint cc_CustomerCheckSum

check (checksum( CustomerID, SalesLeadID ) = CustomerCheckSum)

);

Supply the checksum value when inserting the table:

insert into Customer

values (32, 'John Doe', '555-555-5555', 15, CHECKSUM( 32, 15) );

The columns CustomerName and CustomerPhone can be updated.

update Customer set CustomerPhone = '555-555-1234' where CustomerID = 32;

(1 row(s) affected)

But here we see the result of attempting to update CustomerID or SalesLeadID.

update Customer set SalesLeadID = 20 where CustomerID = 32;

Msg 547, Level 16, State 0, Line 16

The UPDATE statement conflicted with the CHECK constraint "cc_CustomerCheckSum".

Of course the checksum column can also be updated, but it provides at least some degree of deterrent. If the entire record should never be updated, maybe this is a financial application, then you can populate the checksum column using a default constraint and prevent the need to supply checksum value in insert statement.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

terry999 SSCertifiable Points: 5033 More actions · Answer 9

Thank you v.much Eric

That is a good idea, it is simple to understand. I went for the permissions in the end.

All new data is protected. I can still login with admin rights and fix old records. Trigger would have to be disabled for me to do fixes.

I like your idea, because the environment they use to do ad-hoc queries they couldn't call checksum func. So allows fixes but stops any new errors.

I hope I remember this checksum computed column for future next time I need something similar.

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 10

terry999 (10/9/2015)
I hope I remember this checksum computed column for future next time I need something similar.

Careful now. There IS a thing in SQL Server that's actually called a "computed column". Just to avoid any future confusion, this is NOT an instance of a "computed column".

To absolutely prevent all changes, regardless of source, a trigger is the way to go here. If a change must be made by authorized personnel, then a script or "DBA Level" stored procedure to exclusively lock the table, disable the trigger, make the change, reenable the trigger, and unlock the table would be in order.

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

Eric M Russell SSC Guru Points: 125521 More actions · Answer 11

terry999 (10/9/2015)
Thank you v.much Eric
That is a good idea, it is simple to understand. I went for the permissions in the end.
All new data is protected. I can still login with admin rights and fix old records. Trigger would have to be disabled for me to do fixes.
I like your idea, because the environment they use to do ad-hoc queries they couldn't call checksum func. So allows fixes but stops any new errors.
I hope I remember this checksum computed column for future next time I need something similar.

If users are not allowed to update tables at all, then DENY update and delete would be a much simpler and appropriate solution. What I suggested with the checksum would be a scenario where the application may need to update certain attributes like customer's name that are naturally mutable, but you don't want them re-coding certain key columns that should never change once inserted.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

terry999 SSCertifiable Points: 5033 More actions · Answer 12

Point taken. If the checksum val was automatically put in a column - it would be a computed column.

soonerGreg8 Grasshopper Points: 11 More actions · Answer 13

@eric Russel, a checksum doesnt help w/ ad hoc queries or SPs .

triggers have their place, and I think this is when a trigger is called for.

security is also a valid option,

Can i suggest a simple update trigger that sets inserted.manId = deleted.manid join on the table unique key.

Effectively ignore any attempt to update the field. This is super simple and can not be side stepped.

This reply was modified 1 year ago by soonerGreg8.