Fun with Database Triggers and Recursion in RDB

Question

I had a problem this week (which thankfully I've solved in a much better way);

• I needed to keep a couple of fields in a database constant.

So, I knocked up a script to place a Trigger on the table, that would set the value back to a preset number when either an insert, or update took place.

The database is RDB running on VMS (but i'd be interested to know the similarities for SQLServer).

Here are the triggers:

drop trigger my_ins_trig;
drop trigger my_upd_trig;

  !
  !++ Create triggers on MY_TABLE
  CREATE TRIGGER my_ins_trig AFTER INSERT ON my_table
         WHEN somefield = 2
  (UPDATE my_table table1
   SET table1.field1 = 0.1,
       table1.field2 = 1.2
   WHERE  my_table.dbkey = table1.dbkey)
  FOR EACH ROW;

  CREATE TRIGGER my_upd_trig AFTER UPDATE ON my_table
         WHEN somefield = 2
  (UPDATE my_table table1
   SET table1.field1  = 0.1,
       table1.field2  = 1.2
   WHERE  my_table.dbkey = table1.dbkey)
  FOR EACH ROW;

Question Time

I'd would expect this to form an infinite recursion - but it doesnt seem to? Can anyone explain to me how RDB deals with this one way or another...or how other databases deal with it.

[NOTE: I know this is an awful approach but various problems and complexities meant that even though this is simple in the code - it couldn't be done the best/easiest way. Thankfully I haven't implemented it in this way but I wanted to ask the SO community for its thoughts on this. ]

Thanks in advance

Answer 1

edit: It seems Oracle RDB just plain doesnt execute nested triggers that result in recursion. From the paper: 'A trigger can nest other triggers as long as recursion does not take place.' I'll leave the rest of the answer here for anyone else wondering about recursive triggers in other DBs.

Well firstly to answer your question - it depends on the database. Its entirely possible that trigger recursion is turned off on the instance you are working on. As you can imagine, trigger recursion could cause all kinds of chaos if handled incorrectly so SQL Server allows you to disable it altogether.

Secondly, I would suggest that perhaps there is a better way to get this functionality without triggers. You can get view based row level security with SQL Server. The same outcome can be achieved with Oracle VPDs.

Alternatively, if its configuration values you are trying to protect, I would group them all into a single table and apply permissions on that (simpler than row based security).