How to allow update column on table only by certain trigger?

Question

I have 3 tables:

`goods` with collumns `id`,`name`,`quantityTotal`

`buy` with collumns `id`,`date`

`buySpec` with collumns `buyId` reffers to field `id` in `buy` table,

`goodsId` ` reffers to field `id` in `goods` table,

`quantity`

And I want to block manually changing value of `quantityTotal` in `goods` table, this should be done only by trigger which if fired on change in `buySpec` table 

Answer 1

DENY UPDATE ON tbl(goods) TO public

But this will not stop anyone who is member of db_owner or sysadmin.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 2

In this case you need a trigger on your Goods table with the following code

create trigger trGoodsChange ON Goods AFTER INSERT, UPDATE

AS

  IF UPDATE(QuantityTotal) and exists (select 1 from Inserted)

      begin

           raiserror ('Changing QuantityTotal column directly is not allowed!',16,1)

            rollback transacation

           return

       end

And you may need a trigger in the buySpec table for all 3 actions that will update the quantityTotal column.

However, giving the calculated nature of this column I don't think you want to store it in the table at all. Perhaps you want to have it in a view instead.

     

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 3

In this case you need a trigger on your Goods table with the following code

But the trigger will fire when you attempt to update the column from the trigger of buySpec...

This can be worked around by looking at trigger_nestlevel(), or changing the condition to:

   IF UPDATE(QuantityTotal) and exists (select 1 from Inserted) and
      object_id('tempdb..#ImComingFromTheBuySpecTrigger') IS NULL

However, giving the calculated nature of this column I don't think you want to store it in the table at all. Perhaps you want to have it in a view instead.

That's a good point, but that presumes that the underlying volumns are reasonable. Unless you go for an indexed view.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

thx for replaing, but could you explain what this "'tempdb..#ImComingFromTheBuySpecTrigger'" mean, I undertand this "ImComingFromTheBuySpecTrigger" is the name of trigger which should be allowed to change value of 'QuantityTotal' but this not works for me, and what is "tempdb..#"

Answer 5

I think Erland's idea is to create a temp table in the trigger on BuySec table, so in your trigger on Goods table you will know if the 

temporary table exists (which was created in that trigger), so it was the trigger that called update on Goods table and it should be allowed.

Or alternatively he is suggesting to base the check on the trigger nested level.

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 6

My rectification to Naomi's trigger was:

  IF UPDATE(QuantityTotal) and exists (select 1 from Inserted) and
      object_id('tempdb..#ImComingFromTheBuySpecTrigger') IS NULL

In the trigger on the buySpec table, you would have:

  CREATE TABLE #ImComingFromTheBuySpecTrigger(a int NOT NULL)

You would not insert any data or use it. The mere existence of the trigger serves a global flag which bypasses the test in the other trigger.

The notation with 'tempdb..' is needed because temp tables resides in tempdb.

This is certainly an advanced solution, and my comment was more a reply to Naomi than to you.

Then again, permissions on column level easily becomes messy, the temp table may after all be a better solution. And it also prevents sysadmin and db_owner to accidently update the column.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 7

Thx Erland Sommarskog this works great:)