AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |
Back to Blog
Rails postgres app3/6/2023 ![]() ![]() You just have a row-level trigger that runs after any statements that change the rows on the table and insert a row into an audit table with all the same columns from your original table and a timestamp as to when this change was made and you will now be able to see any time any data in that table changes. Auditingīasically, if you ever want to store the history of a table for whatever reason, a PostgreSQL trigger might be the way to go. These are just a couple of the more common techniques I have used in my past experience. There are numerous things you can do with PostgreSQL triggers as I am sure you can imagine. You can also make this a statement-level trigger by replacing ROW with STATEMENT. You can mix this up by replacing BEFORE with AFTER or INSTEAD OF or you change the statement types being called. In the example, the trigger function will run before an INSERT or an UPDATE statement that is called on the example_table. In order to add a trigger to your table in raw SQL land, you must first write a function for the trigger to call and then create the trigger like in the below example: I am mainly going to focus on row-level triggers for the rest of this article since that is the one I have the most experience in using. Row-level triggers run the function for every row affected by the statement while statement-level triggers run the function just once per statement. There are two types of triggers to be familiar with in PostgreSQL: PostgreSQL triggers are functions that are caused to run by an INSERT, UPDATE, DELETE, or TRUNCATE statement. If you are already well-acquainted with PostgreSQL triggers, I recommend jumping ahead to here. There does seem to be a decent number of people in the Rails community that are either completely unaware of PostgreSQL triggers or have limited experience with them. If you so desire however, there is one way to move some of that functionality over to the database and that is PostgreSQL triggers. It is more so that we tend to keep functionality in the app rather than in the database. ![]() I personally have written raw SQL for several database migrations in my short time here. That led me to slowly noticing the tendency to avoid SQL that often comes with being a Rails developer. Then, I started working with Viget and was working regularly with Rails for the first time in a professional setting. I come from a background in which we used PostgreSQL triggers on a regular basis. Rest assured, I will get to that, but first a little about me. If you are reading this article, you are probably hoping to learn something about PostgreSQL triggers and how they interact with Rails. ![]()
0 Comments
Read More
Leave a Reply. |