# Database Triggers?

**URL:** <https://www.ruby-forum.com/t/database-triggers/65498>\
**Category:** Rails\
**Created:** [August 18, 2006, 7:26pm UTC](https://www.ruby-forum.com/t/database-triggers/65498 "2006-08-18T19:26:44Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![bradrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@bradrice](https://www.ruby-forum.com/u/bradrice)\
**Post date:** [August 18, 2006, 7:26pm UTC](https://www.ruby-forum.com/t/database-triggers/65498/1 "2006-08-18T19:26:44Z")

</div>

Hi,

I’m new to RoR and exploring its use with an existing application. One  
thing I can’t seem to find much detail on is using database triggers.  
All RoR examples and tutorials I’ve seen are very basic saving data back  
to a single table. My use case includes a somewhat denormalized DB that  
uses triggers to store data in multiple tables on occassion. I  
currently use IBM DB2 9.

Can anyone explain how to use DB triggers in a complimentary fashion  
with RoR? Or, I’m assuming that because I could not find examples of how  
to work with triggers, the more elegant RoR model-based approach on how  
to go about this?

As an example. I have a table called “new\_books”, where a user can  
enter a new book through a form. This new book must be validated by an  
admin before it can be moved to the (validated) “book” table. Normally,  
I would use a trigger.

Another example would be collecting data from a web from where the  
majority is stored in its appropriate DB table but a few items should  
also be stored in one or more of my denormalized tables as needed for  
performance reasons. Again, normally I would use a trigger to  
accomplish this.

How would one go about achieving these tasks with RoR without triggers?

Thanks in advance for any advice.

-Shim

---

<div class="post-metadata">

**Author:** ![bradrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@bradrice](https://www.ruby-forum.com/u/bradrice)\
**Post date:** [August 18, 2006, 9:06pm UTC](https://www.ruby-forum.com/t/database-triggers/65498/2 "2006-08-18T21:06:16Z")

</div>

Brad,

I’m going through the same types of questions : however my background is  
DB2-centric.

I’ve got a couple of comments to make that hopefully are helpful.

Your first issue, with books, would probably be helped by a change to  
your  
table design. “New Books” and “Validated Books” aren’t two separate  
entities (objects) but different states of the one object. You should  
probably have a column for “book status” (set initially to “Needs  
Validation”). When the admin validates the book you’d then have a very  
simple validated() method to update this status column. You can create  
separate methods in your controller (or model ?) to retrieve only  
validated  
books or books awaiting validation for display. In the RoR way, you’d  
just  
add the condition to the find method. An alternative would be to  
create  
views over the table which simulate the tables you originally had.

As far as DB2 is concerned, it is fairly uncommon now to need to  
denormalize  
for performance reasons. Much better to tune the database a little bit  
by  
adding appropriate indexes. Look at Design Advisor if you need help  
with  
this (or give me a call ).

To comment on the trigger question, it is still my opinion that you  
should  
continue to use triggers for certain things : especially where these  
actions  
are mandatory across all uses of the table. Validation and maintaining  
audit trails are two very good examples. But coming from a database  
background I probably am biased. If these things are not coded at the  
database level then someone accessing the database outside your  
application  
(e.g. from a command line) wouldn’t invoke them.

I believe there needs to be enhancements to RoR / ActiveRecord to allow  
it to  
“play nice” with database encoded rules. It looks like “Dr Nic’s Magic  
Models” is going some way at least towards this, but I think there is  
more to  
do. The database adaptors should be able to be used to feed back a  
lot of  
information into the RoR models which would eliminate a lot of  
duplication.  
For example using your foreign keys, check constraints etc to drive  
validation.

I think there also needs to be more effort to support legacy database  
designs  
and designs which use natural keys. At the moment I’m working on a  
system  
which uses the standard ISO country definitions. It is totally  
pointless to  
generate an integer ID as well as the two character country code. But  
you  
have to jump through too many hoops to make the two character code your  
primary key. Even worse if you want to use a composite natural key :  
although again there is a gem that is supposed to help with this (I’ve  
still  
to look at that).

HTH

Phil Nelson  
removed\_email\_address@domain.invalid

---

<div class="post-metadata">

**Author:** ![bradrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@bradrice](https://www.ruby-forum.com/u/bradrice)\
**Post date:** [August 19, 2006, 1:15am UTC](https://www.ruby-forum.com/t/database-triggers/65498/3 "2006-08-19T01:15:39Z")

</div>

Unfortunately, my scenario is a bit more complicated. The “New Books”  
table has multiple columns that are used in the validation stages that  
aren’t in the “Books” table…No point in diving deeply into the DDL  
but a single bit column doesn’t make sense in this scenario…

Same thing goes for the other scenario. My application has some  
significant OLAP needs that absolutely require some  
denormalization…otherwise, it would require far too many table joins  
and horsepower. Indexes aren’t the answer. It is significantly more  
efficient to denormalize some data into a single table.

Let me simplify my question. Let’s say I have a form with values X,Y,Z  
that are saved into table A. I also need value X and Y in table B. How  
does one accomplish this in RoR without using triggers?

Thanks

---

<div class="post-metadata">

**Author:** ![bradrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@bradrice](https://www.ruby-forum.com/u/bradrice)\
**Post date:** [August 19, 2006, 2:00am UTC](https://www.ruby-forum.com/t/database-triggers/65498/4 "2006-08-19T02:00:31Z")

</div>

Perfect.

Thanks for the feedback Dave. That’s excactly what I needed to know.

---

<div class="post-metadata">

**Author:** ![bradrice](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/48db29/32.png) [@bradrice](https://www.ruby-forum.com/u/bradrice)\
**Post date:** [August 19, 2006, 1:27am UTC](https://www.ruby-forum.com/t/database-triggers/65498/5 "2006-08-19T01:27:39Z")

</div>

On 19/08/06, Brad R. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:  
…

> Let me simplify my question. Let’s say I have a form with values X,Y,Z  
> that are saved into table A. I also need value X and Y in table B. How  
> does one accomplish this in RoR without using triggers?

In your controller, once the user has entered data into your form:

def save\_form\_data  
a = A.create(:field1 =\> X, :field2 =\> Y, :field3 =\> Z).save  
b = B.create(:field4 =\> X, :field5 =\> Y).save  
end

where A is a model referencing your ‘table A’, and B is a model  
referencing your ‘table B’.

Regards

Dave M.