Databases
StackOverflow2013
Postsdbo
POAltering PostgreSQL table in Rails app disables trigger
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAltering PostgreSQL table in Rails app disables trigger
    primarykey
    data
    text
    <p>In my Rails 3.1.3 app I have <code>subscriptions</code> table; operations on this table must be tracked which is crucial for billing. Since database can be accessed in many ways (API, console, client apps), simple ActiveRecord callbacks or observer is not enough to make sure all transactions on a table are recorded. Therefore, I create a database trigger on <code>subscriptions</code> table that inserts a record in "log" table anytime something changes. In order to do that I am using Rails migration like this: </p> <pre><code>def up execute &lt;&lt;-SQL CREATE OR REPLACE FUNCTION FUNCTION_Event_Type() RETURNS TRIGGER AS $TRIGGER_Event_Type$ BEGIN IF (TG_OP = 'DELETE') THEN INSERT INTO subscription_log (company_id, product_id, old_package_id, new_package_id, trigger_type, updated_at, created_at) SELECT OLD.company_id, OLD.product_id, OLD.package_id, NULL, 'Delete', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP; RETURN OLD; ELSIF (TG_OP = 'UPDATE') THEN INSERT INTO subscription_log (company_id, product_id, old_package_id, new_package_id, trigger_type, updated_at, created_at) SELECT OLD.company_id, OLD.product_id, OLD.package_id, NEW.package_id, 'Update', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP; RETURN NEW; ELSIF (TG_OP = 'INSERT') THEN INSERT INTO subscription_log (company_id, product_id, old_package_id, new_package_id, trigger_type, updated_at, created_at) SELECT NEW.company_id, NEW.product_id, NULL, NEW.package_id, 'Insert', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP; RETURN NEW; END IF; RETURN NULL; END; $TRIGGER_Event_Type$ LANGUAGE plpgsql; CREATE TRIGGER TRIGGER_Event_Type AFTER INSERT OR UPDATE OR DELETE ON subscriptions FOR EACH ROW EXECUTE PROCEDURE FUNCTION_Event_Type(); SQL end </code></pre> <p>The trigger works fine and logs as needed. I have rspec tests checking that record is inserted in "logging" table anytime something is done on <code>subscriptions</code> table. However, I continued working on the app, and had to add a column to <code>subscriptions</code> table using Rails migration:</p> <pre><code>def change add_column :subscriptions, :description, :text end </code></pre> <p>After I ran the migration, my tests that check trigger functionality like this: </p> <pre><code> lambda do FactoryGirl.create(:subscription) end.should change(SubscriptionLog, :count).by(1) </code></pre> <p>started failing.</p> <p><em>UPDATE</em>: development database still has trigger after adding a column. test database loses trigger after running the migration that adds a column... Weird</p> <p>Question:<br> Does altering the table kill a trigger? And if it does, what would be the way to make sure trigger persists?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSimon Bagreev
    1. USSimon Bagreev
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAltering PostgreSQL table in Rails app disables trigger
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAltering PostgreSQL table in Rails app disables trigger
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHow exactly did you "add a column to `subscriptions`"? Please show the code. Column name is relevant, too. And how does it "start failing"? Any error messages? Trigger is still in place?
      singulars
      1. POAltering PostgreSQL table in Rails app disables trigger
      1. USErwin Brandstetter
    2. COYou have this problem only because you have a bad architecture. Your apps, console, etc, should use the api(could be the rails app), and there would be the only place where business rules would live. Not in the database.
      singulars
      1. POAltering PostgreSQL table in Rails app disables trigger
      1. USIsmael Abreu
    3. COI disagree with @ismaelga. A trigger in the database is the safest method if multiple tiers can access the database. Business rules in the app only - that is inherently unsafe.
      singulars
      1. POAltering PostgreSQL table in Rails app disables trigger
      1. USErwin Brandstetter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload