How do I get "action" information for a foreign key from the system tables:
After I create a foreign key with one of these attributes, which system tables can I query so that I can reverse engineer?
Note that using some tool to do this is not an option. I need to query the system tables.
SYSTRIGGER is your friend...
SELECT SYSTRIGGER.event, SYSTRIGGER.referential_action, * FROM SYSFOREIGNKEY LEFT OUTER JOIN SYSTRIGGER ON SYSTRIGGER.foreign_table_id = SYSFOREIGNKEY.foreign_table_id AND SYSTRIGGER.foreign_key_id = SYSFOREIGNKEY.foreign_key_id AND SYSTRIGGER.event IN ( 'C', 'D' );
Note the LEFT OUTER JOIN since RESTRICT actions don't have rows in SYSTRIGGER.
Here's what the Help says about those columns:
event The event or events that cause the trigger to fire. This single-character value corresponds to the trigger event that was specified when the trigger was created. A - INSERT, DELETE B - INSERT, UPDATE C - UPDATE COLUMNS D - DELETE E - DELETE, UPDATE I - INSERT U - UPDATE M - INSERT, DELETE, UPDATE referential_action The action defined by a foreign key. This single-character value corresponds to the action that was specified when the foreign key was created. C CASCADE D SET DEFAULT N SET NULL R RESTRICT
To repeat... don't go looking for SYSTRIGGER.referential_action = 'R', it's a magical phantom value, like the pot of gold at the end of the rainbow... i.e., it doesn't exist in reality. It's the default, SQL Anywhere don't need no steenking trigger to throw an exception :)
answered 02 Apr '10, 09:45