Hi all,
Short version of question:
I frequently want to alter view definitions, such as renaming them or
changing the column definitions. The only way to do this is to drop
the old trigger and create a new one (eg with the same name). The
problem is that when I do this, any associated triggers are deleted by
SQLite. What's the best way to keep the associated triggers?
Long version of question:
I can rename or add a column to a table (using SQLite's "alter table"
command"). But to rename or add a column to a view I have to drop the
view and recreate it. And if I want to to alter existing columns in a
table, I have to drop the table and recreate it. That's fine, I can
cope with that.
My problem is that, to my mind, SQLite seems inconsistent in how it
deals with triggers compared to views, when a precedent table or view
is deleted. Here are the actions I take and the effects in SQLite:
Action Effect on associated views Effect on
associated triggers
------ --------------------------
-----------------------------
Rename table None Rename table
references
Drop table None Delete trigger
Drop view None Delete trigger
I would rather than SQLite consistently did not change any dependent
triggers when I changed a precedent table or view (ie "none" in all
the grid cells above). That would be consistent with how it treats
dependent views. ie SQLite seems happy to keep views that no longer
link to anything, but not triggers. And it's easier for me to
explicitly delete triggers, if I want to, than try to find out what
triggers will be affected, cache them somehow, perform my action, and
reconstitute the triggers.
As a specific case, when I want to change existing columns in a table
or view, the only way to do that in SQLite is to cache the old
definition, drop the table or view, edit the cached definition
(changing the columns) and recreate it. That's fine. But now all the
associated triggers have disappeared. Or, in psuedo code:
set viewDefinition to SQLExec( "select sql from sqlite_master where
name = 'MyView';" )
SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )
As mentioned, all the associated triggers are deleted. IS there a way
to systematically locate and keep them? Such as:
set viewDefinition to SQLExec( "select sql || ';' from sqlite_master
where type = 'view' and name = 'MyView';" )
set triggerDefinitions to SQLExec( "select sql || ';' from
sqlite_master where type = 'trigger' and sql like '%on MyView%';" )
SQLExec( "drop view 'MyView';" )
set viewDefinition to ChangeColumnDefinitions( viewDefinition )
SQLExec( viewDefinition )
SQLExec( triggerDefinitions )
However, that would fail because the test "like '%on MyView%'" is not
robust or accurate enough.
Any other ideas or comments? How do you approach the issue, or don't
you have the need to alter views with associated triggers?
Thanks,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
