How do I get "action" information for a foreign key from the system tables:

E.g.

...
on update Set null
on delete set null;

...
on update CASCADE
on delete CASCADE;

...
on update CASCADE
on delete CASCADE;

...
on update restrict
on delete restrict;

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.

Thanks,

asked 01 Apr '10, 23:26

Brad%20Wery's gravatar image

Brad Wery
382182126
accept rate: 0%

edited 06 Apr '10, 13:25

Volker%20Barth's gravatar image

Volker Barth
30.0k294448654


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 :)

permanent link

answered 02 Apr '10, 09:45

Breck%20Carter's gravatar image

Breck Carter
27.4k424585837
accept rate: 21%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×24
×22

question asked: 01 Apr '10, 23:26

question was seen: 993 times

last updated: 06 Apr '10, 13:25