|
What is Aruna DB? |
Last Updated: 9/25/2001
A_Triggers
This Ruby module contains methods for providing trigger support the A_Table class. Triggers are methods that you create and attach to one of the trigger events associated with each table. Triggers allow you to check data, update other pieces of data, and make sure the data is consistent before and after insert, updates and deletes. There can be many triggers installed on each table. When there are multiple triggers, they are called in the order they were added. The A_Trigger module is an extension to the A_Table_Shared class (this is the shared portion of the A_Table class). Triggers are an extension of the A_Table_Shared class. Triggers are used by the A_Table class.
Triggers are Ruby code (methods that you create) and to tables at run time. As a result, triggers are not stored in the system catalog. Triggers can be disabled at in each transaction. The trigger events for every table are:
- before insert - this is called right before a row is inserted into the table. This is useful for checking the content of the row before it is inserted. Simple checks can be done with column constraints. Complex checks and references to data in other tables should be performed in this trigger. If something looks incorrect, you can raise an error.
- before update - this is called right before data is updated in the table. This is useful for checking the content of the row before it is updated. Simple checks can be done with column constraints. Complex checks and references to data in other tables should be performed in this trigger. If something looks incorrect, you can raise an error.
- before delete - this is called right before a row is deleted from the table. This is useful for checking the content of the row before it is inserted. You can use this trigger to check for data in other tables that may rely on this record and raise an error if necessary.
- after insert - this is called right after a row is inserted into the table. This is useful when you want to insert or update information in other tables based on the result of this insert. If something looks incorrect, you can raise an error.
- after update - this is called right after a row is updated in the table. This is useful when you want to insert or update information in other tables based on the result of this update. If something looks incorrect, you can raise an error.
- after delete - this is called right after a row is deleted from the table. This is useful for deleting other rows in other tables that may be dependent on this row. If something looks incorrect, you can raise an error.
The following new error has been created in this module for triggers:
- TriggerError - you can use this when you raise an error inside a trigger method.
Here are some general guidelines you should use when creating trigger methods:
- Change the data only in the before event triggers
- Rasie errors only in the before event triggers.
- Update other tables only in the after event triggers
- Never raise an error in the after event triggers.
# create a table
cols = []
cols.push(A_Column.new('member_id', nil, true, nil, '%d > 0', nil, '%3d'))
cols.push(A_Column.new('last_name', nil, true, nil, "'%s' != ''", "'%s'.capitalize", '%-12s'))
cols.push(A_Column.new('first_name', nil, nil, '', nil, "'%s'.capitalize", '%-12s'))
m = A_Table.new('Membership', cols, 'member_id')
# create a method for the trigger event
def membership_before_update(old_array, data_array)
print "calling membership_before_update(#{old_array}, #{data_array})\n"
end
# create and install the trigger using the method above, the trigger name is 'tst_after_insert'
m.create_trigger('tst_after_insert', TRIGGER_AFTER_INSERT, 'membership_after_insert')
m.close
- Insert triggers (before and after) require one and only one parameter. The parameter is the A_Table_Data object of the row being inserted. This allows you to check the data both before and after the insert has occurred. Here is an example:
def membership_before_insert(data_array)
if (data_array.first_name.downcase == 'josh')
raise TriggerError, "membership_before_insert() trigger error on before insert, name should never = 'Josh'"
end
# this actually changes the data being inserted
data_array.status = 19 if (data_array.status == nil)
end
- Update triggers (before and after) require two and exactly two parameters. The first parameter is the A_Table_Data object of the row before the update. The first parameter is the A_Table_Data object of the row after the update. This allows you to see the changes in the row and to check the data both before and after the update has occurred. Here is an example:
def membership_before_update(old_array, data_array)
data_array.status = 19 if (data_array.status == nil)
if (data_array.status_id != old_array.status_id)
raise TriggerError, "membership_before_update() trigger error, you should never update the status_id"
end
end
- Delete triggers (before and after) require one and only one parameter. The parameter is the A_Table_Data object of the row being deleted. This allows you to check the data both before and after the delete has occurred. Here is an example:
def membership_before_delete(old_array)
if (old_array.status_id == 19)
raise TriggerError, "membership_before_delete() trigger error, you should never delete row where the status_id == 19"
end
end
create_trigger(trigger_name, trigger_type, trigger_method_name)
Added a trigger method to this table. A table can have many triggers methods installed on it. When a single event has multiple methods installs, the methods are called in the order they were installed.
- trigger_name - the name of trigger you are creating. This name can be used to delete this trigger later. The more descriptive the name the better.
- trigger_type - the event that you what this trigger installed on. Here are the trigger_type constants that you can use:
- TRIGGER_BEFORE_INSERT
- TRIGGER_BEFORE_UPDATE
- TRIGGER_BEFORE_DELETE
- TRIGGER_AFTER_INSERT
- TRIGGER_AFTER_UPDATE
- TRIGGER_AFTER_DELETE
- trigger_method_name - this is the name of the method that you create. These methods must conform the guidelines below or you will get an error when the trigger method is invoked. See the usage section above for guidelines on creating trigger methods.
drop_trigger(trigger_name, trigger_type)
Drop a trigger.
- trigger_name - the name of trigger you want to drop.
- trigger_type - the event that you what this trigger removed from. Here are the trigger_type constants that you can use:
- TRIGGER_BEFORE_INSERT
- TRIGGER_BEFORE_UPDATE
- TRIGGER_BEFORE_DELETE
- TRIGGER_AFTER_INSERT
- TRIGGER_AFTER_UPDATE
- TRIGGER_AFTER_DELETE
show_triggers(prefix='')
Prints all triggers installed on this table. Prefix can be used to indent the output for nicer formatting.
triggers_exist()
Returns true if any triggers are installed on this table. Returns false if no triggers are installed on this table.
tst_a_triggers.rb - this script show how to create and install triggers on tables. To run this tests type:
ruby -I.. tst_a_ triggers.rb
10 - prints information about creating and dropping triggers.