0

I have two tables, t1(foo) and t2(bar), and a trigger on update t2.bar that updates t1.foo to something else.

At first I thought of just prohibiting any updates on t1 by using a trigger before update on t1.foo that always throws an exception. However, wouldn't that also block changes from the first trigger?

How do I do this?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
leinaD_natipaC
  • 4,299
  • 5
  • 21
  • 40
  • 1
    Are you trying to prevent access to tables with triggers? Don't do that. Use the built in security system (i.e. logins, roles etc) – adrianm Nov 21 '13 at 13:18
  • That sounds like it could be a good answer if explained, along with some usage philosophy. – leinaD_natipaC Nov 23 '13 at 23:38

1 Answers1

1

Use a "daemon" role (a non-login role for this dedicated purpose) that owns the trigger function in combination with SECURITY DEFINER And grant the necessary privileges on t1 to it.

Details in these related questions:
Is there a way to disable updates/deletes but still allow triggers to perform them?
Allow insertion only from within a trigger

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228