Re: [sqlite] Triggers and TEMP tables: ticket #1689

[K. Haley]

[EMAIL PROTECTED] wrote:
> A user complains in ticket #1689 that SQLite does not
> allow triggers in one database to refer to tables in
> a different database or to TEMP tables.
...
> Should I remove the tests from SQLite that prevent
> triggers in one database from referring to tables in
> a different database?  Or should I leave things as
> they are and close ticket #1689 with a remark of
> "works as designed".
I don't really see any reason to do it.  When I use attach, I create a
function to open the main db and attach any extra db's as well as add
any temp triggers I may need.  I couldn't find anything in the ticket to
indicate that the user couldn't use this method.  After all, there's
nothing to prevent someone from dropping the trigger, altering the db,
the re-creating the trigger if a persistent version were allowed.




signature.asc
Description: OpenPGP digital signature

RE: [sqlite] Triggers and TEMP tables: ticket #1689

[Cariotoglou Mike]

I am not sure how this would work. in order for a trigger to "See"
another database, somebody (obviously not the trigger) must ATTACH the
database first. is there an automatic ATTACH capability that I am not
aware of ? if not, specifying triggers than span databases is looking
for trouble IMHO. as for allowing it, well, guns are allowed also, so
you can shoot yourself in the foot, if you really want to. 

> -Original Message-
> From: Brad [mailto:[EMAIL PROTECTED] 
> Sent: Tuesday, February 28, 2006 3:28 PM
> To: sqlite-users@sqlite.org
> Subject: Re: [sqlite] Triggers and TEMP tables: ticket #1689
> 
> > Should I remove the tests from SQLite that prevent triggers in one 
> > database from referring to tables in a different database?
> 
> Well, I have a couple of triggers in my SQL Server database 
> that refer to tables in another database, though they are on 
> the same server. 
> Obviously, I can see some utility in that.  I will admit, 
> however, that if I had to implement the system that uses that 
> scheme in SQLite, then I'd probably have the tables in the 
> same database file, rather than different ones.
> 
> 
> 
> 

Re: [sqlite] Triggers and TEMP tables: ticket #1689

[Brad]

Should I remove the tests from SQLite that prevent
triggers in one database from referring to tables in
a different database?


Well, I have a couple of triggers in my SQL Server database that refer 
to tables in another database, though they are on the same server. 
Obviously, I can see some utility in that.  I will admit, however, that 
if I had to implement the system that uses that scheme in SQLite, then 
I'd probably have the tables in the same database file, rather than 
different ones.

Re: [sqlite] Triggers and TEMP tables: ticket #1689

[Derrell . Lipman]

[EMAIL PROTECTED] writes:

> Should I remove the tests from SQLite that prevent
> triggers in one database from referring to tables in
> a different database?  Or should I leave things as
> they are and close ticket #1689 with a remark of
> "works as designed".

I have had use for triggers that could operate on tables in a different
database, and been unable to use them.  I separate tables into two primary
databases because the tables in one of them can be locked for long periods of
time (long transactions) while the tables in the other database are accessed
frequently.  The mechanism for determining which databases will be operated on
is external to SQLite, so I would know that it is safe to issue a request that
would cause a trigger to operate on the other database.

I'd vote to allow a trigger to operate on a table in a different database, if
there are no reasons other than "they shouldn't need to do that" preventing
it.

Derrell