SAP SQL Anywhere

Question

Is there any way to automatically validate system triggers to see if I have broken any by renaming columns in foreign keys?

It upset me to discover whilst investigating a bug today the following statement in the docs, which I was not aware of previously:

RENAME column-name TO new-column-name:

Change the name of the column to the new-column-name. Note that any applications using the old column name will need to be modified, as necessary. After the renaming operation succeeds, foreign keys with ON UPDATE or ON DELETE actions must be dropped and re-created, as the system-created triggers used to implement these actions will continue to refer to the old name.

Hopefully I haven't done this in too many places, but hunting through every system trigger doesn't fill me with excitement ...

As a sidenote - why does it not just prevent you from renaming columns in foreign keys as it does with primary keys?

EDIT: This is using 11.0.1.2352. It definitely doesn't seem to be correcting the system triggers automatically on rename!

EDIT to provide an example as requested:

Create a DB (using Sybase Central wizard defaults):

CREATE TABLE A (
     AID                      integer NOT NULL DEFAULT autoincrement
    ,AName                    text NULL
    ,PRIMARY KEY (AID)
)
go

CREATE TABLE B (
     BID            integer NOT NULL DEFAULT autoincrement
    ,BName          text NULL
    ,PRIMARY KEY (BID)
)
go

CREATE TABLE C (
     CID            integer NOT NULL DEFAULT autoincrement
    ,A              integer NOT NULL
    ,B              integer NOT NULL
    ,PRIMARY KEY (CID)
    ,FOREIGN KEY Foo (A)
         REFERENCES A (AID)
         ON UPDATE CASCADE 
         ON DELETE CASCADE
    ,FOREIGN KEY Bar (B)
         REFERENCES B (BID)
         ON UPDATE CASCADE 
         ON DELETE CASCADE
)
go

Then run the following SQL statement:

ALTER TABLE C
RENAME A TO ARenamed;

Oddly, what then appears to happen is that the update columns system trigger is deleted. The delete trigger remains but with the wrong key name (as the docs suggest should happen). This is with 11.0.1.2427.

Accepted Answer

3

Starting with 10.0.1, the server will automatically correct system triggers when a column in a primary key or foreign key is renamed.

Update: Thanks for the example.

Looks to be a bug that affects ON UPDATE as well as foreign keys that specify both ON UPDATE and ON DELETE.

To detect any problems, you can execute:

dbunload -n -an new.db -c uid=DBA;pwd=xxx;dbf=old.db

to copy the schema (no data) and generate correct triggers. Then execute:

unload select table_name,trigger_defn 
from SYSTRIGGER tr
    join SYSTAB t on (tr.table_id = t.table_id)
where foreign_table_id is not null 
order by tr.table_id,foreign_table_id,foreign_key_id,event
to 'trigdefs.txt'
quotes off
escapes off

on both the original and new databases (changing the output filename). Then diff the files. Unfortunately, this will not detect those triggers that have been deleted. If you have a backup from before the columns were renamed, you could perform the same process using that copy of the database.

This problem has been fixed in:

12.0.0.2624
11.0.1.2537
10.0.1.4162

permanent link

answered 15 Dec '10, 16:43

Bruce Hay
2.6k●1●5●10
accept rate: 48%

edited 16 Dec '10, 00:23

This doesn't appear to be the case to me using 11.0.1

(15 Dec '10, 16:50) Adam King

Please provide an example that shows the problem.

(15 Dec '10, 17:56) Bruce Hay

1

Edited to provide example, but are you saying the docs are wrong on this?

Whether or not the problem is a bug or by design, is there any way I can test all the system triggers which exist in my DB to ensure there aren't any which are broken?

(15 Dec '10, 19:21) Adam King

Thanks Bruce - this helped find the issues. There were only a couple in the end thankfully.

(16 Dec '10, 23:41) Adam King

flat view

SAP SQL Anywhere

Checking validity of System Triggers / Renaming Foreign Keys

Follow this question

Related questions