CODEWITHSUNDEEP
sqlsql-serverdatabase
TJF
TJF

Reputation: 1101

Is there a way to make a field only editable by a trigger?

In Sql Server 2008 is there a way to set a field that it can only be changed using a Trigger?

Example:

When you create a record it is set to NULL and then updated by a trigger to e.g 1. It should not be able to be set to anything other than NULL by the user. And then when it is updated the trigger will set the value to 2.

Upvotes: 0

Views: 253

Answers (2)

JdMR
JdMR

Reputation: 1266

Have you considered using using computed columns. If you want data in this column to be only governed by database logic then computed columns are probably way to go.

Upvotes: 0

muhmud
muhmud

Reputation: 4604

create table tmp (a int primary key, b int)
GO

create trigger tr_insupd_tmp on tmp
instead of insert, update
as
    if not exists(select * from deleted)
        -- Process Insert
        insert into tmp
            select a, 1
            from inserted
    else
        -- Process Update
        update tmp
            set b = 2
        from tmp t
        inner join inserted i on t.a = i.a
GO

Upvotes: 1

Related Questions