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:
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 184.108.40.2062. 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):
Then run the following SQL statement:
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 220.127.116.117.
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:
to copy the schema (no data) and generate correct triggers. Then execute:
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: