Hi Tim,

Can this be done:

I have a trigger which is supposed to delete from various detail tables
.... only if some conditions outside the trigger are true. For example
(in an application very similar to Visual Sourcesafe) I need the
trigger to check wheather a specific user is logged in. That is not a
Windows user logged in, but an application specific user logged in
(think how a user logs in Visual Source Safe for example).

So, when the application deletes a record, I think what I need is to
start a transaction there and set a variable, say UserRef=5 ... which
than later the trigger can check. Now the trick is for example what do
*you* do when the variable was not set at all ... i.e. I want to delete
a record from edb manager which has no clue about my transaction
variable. In this case the trigger should fail and the delete should
fail.

In my practical example, I have files that are shared among projects.
The accounting is maintained by a "shares" integer field which I
increase when a file is shared and decrease when the file is "deleted"
from a project, when shares=0 I can delete all details. I know some
will say I should keep a table with share references, but that will
slow down probably my view data collection. I know a file is shared
without having to join some other table.

BTW, this application works fine in DBISAM because there are no
triggers there, the application does all the maintenance.

Basically to recap I need something like

START TRANSACTION
DECLARE GLOBAL txUserRef, txProjectRef INTEGER;
SET txUserRef=5;
SET txProjectRef=1426;
DELETE FROM files WHERE Files.RecRef=536;
COMMIT/ROLLBACK crap;
UNDECLARE txUserRef, txProjectRef; /* in a try-finally block */


in the trigger before delete for the table "files" I would be able to
do this

DELETE FROM filesout
WHERE (FileRef=:FileRef)        /* don't bother with this*/
 AND (UserRef=txUserRef)       /* this is my problem */
 AND (ProjectRef=txProjectRef);/* and also this is my problem */



--
thanks
Lucian

Why not just create a table where you store the "global" variables. Then in
a trigger you're simply querying/inserting into a table which you can do.

Raul

"Lucian Radulescu" <lucianATez-delphiDOTcom> wrote in message
news:xn0giqpnhi7zn7000@news.elevatesoft.com...
> Hi Tim,
>
> Can this be done:
>
> I have a trigger which is supposed to delete from various detail tables
> ... only if some conditions outside the trigger are true. For example
> (in an application very similar to Visual Sourcesafe) I need the
> trigger to check wheather a specific user is logged in. That is not a
> Windows user logged in, but an application specific user logged in
> (think how a user logs in Visual Source Safe for example).
>
> So, when the application deletes a record, I think what I need is to
> start a transaction there and set a variable, say UserRef=5 ... which
> than later the trigger can check. Now the trick is for example what do
> *you* do when the variable was not set at all ... i.e. I want to delete
> a record from edb manager which has no clue about my transaction
> variable. In this case the trigger should fail and the delete should
> fail.
>
> In my practical example, I have files that are shared among projects.
> The accounting is maintained by a "shares" integer field which I
> increase when a file is shared and decrease when the file is "deleted"
> from a project, when shares=0 I can delete all details. I know some
> will say I should keep a table with share references, but that will
> slow down probably my view data collection. I know a file is shared
> without having to join some other table.
>
> BTW, this application works fine in DBISAM because there are no
> triggers there, the application does all the maintenance.
>
> Basically to recap I need something like
>
> START TRANSACTION
> DECLARE GLOBAL txUserRef, txProjectRef INTEGER;
> SET txUserRef=5;
> SET txProjectRef=1426;
> DELETE FROM files WHERE Files.RecRef=536;
> COMMIT/ROLLBACK crap;
> UNDECLARE txUserRef, txProjectRef; /* in a try-finally block */
>
>
> in the trigger before delete for the table "files" I would be able to
> do this
>
> DELETE FROM filesout
> WHERE (FileRef=:FileRef)        /* don't bother with this*/
>  AND (UserRef=txUserRef)       /* this is my problem */
>  AND (ProjectRef=txProjectRef);/* and also this is my problem */
>
>
>
> --
> thanks
> Lucian
>
> __________ Information from ESET NOD32 Antivirus, version of virus
> signature database 4676 (20091210) __________
>
> The message was checked by ESET NOD32 Antivirus.
>
> http://www.eset.com
>
>
>

__________ Information from ESET NOD32 Antivirus, version of virus signature database 4676 (20091210) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

Raul wrote:

> Why not just create a table where you store the "global" variables.

Interesting ideea. I'll check what's more feasible, cuz what I did so
far was I abandoned the trigger in favour of a procedure. But it'll be
some time until I'll have some data in my database.

Thanks Raul

Lucian


I'd go along with Raul, but also

>DELETE FROM filesout
>WHERE (FileRef=:FileRef) /* don't bother with this*/
> AND (UserRef=txUserRef) /* this is my problem */
> AND (ProjectRef=txProjectRef);/* and also this is my problem */

If txUserRef is the currently logged user (assuming you log your users individually into ElevateDB) then (UserRef=txUserRef)  would become (UserRef=CURRENT_USER)

txProjectRef might be a bit of a problem but you can either write the value into a control file and pick it up using ElevateDB's standard functionality or write it to a text file and write a UDF to retrieve it.

This

TRIGGER "ContactsAfterUpdate"
BEGIN
DECLARE ShortName VARCHAR;
DECLARE LongName VARCHAR;
DECLARE LogLevel VARCHAR;
DECLARE Logging SENSITIVE CURSOR FOR LogTbl;
DECLARE Naming SENSITIVE CURSOR FOR Names;
DECLARE Config SENSITIVE CURSOR FOR ConfigParams;
DECLARE BatchNo GUID;

PREPARE ConfigParams FROM 'SELECT CAST(_ParamData AS VARCHAR(5) ) AS Level FROM Config WHERE _ID = ''alContacts''';

OPEN Config;
FETCH FROM Config(Level) INTO LogLevel;

IF LogLevel <> 'None' THEN....

shows how to obtain a control value from a table for use in a trigger.

If you want more just ask.

Roy Lambert [Team Elevate]

Lucian


Just a bit more about the CURRENT_USER thing. I have a user table (called Staff these days to stop me being confused with ElevateDB's Users table). Staff has the same ID as Users. I use ELevateDB's built in facilities to log people on, and keep all the additional stuff (full name, job title, email etc) in Staff. Because if the 1:1 correspondence between Users and Staff I can use CURRENT_USER throughout my app.

Roy Lambert

Raul,

<< Why not just create a table where you store the "global" variables. Then
in a trigger you're simply querying/inserting into a table which you can do.
>>

In addition, I would also specifically recommend using a temporary table,
which is automatically removed after a session terminates.

--
Tim Young
Elevate Software
www.elevatesoft.com

> If txUserRef is the currently logged user (assuming you log your
> users individually into ElevateDB)

No, it's not. It's an application table, with just few fields: Name,
Password, FavoriteProject, IsAdmin.


> shows how to obtain a control value from a table for use in a trigger.

I'm saving this to my EDB Scripts folder

thx,
Lucian

> In addition, I would also specifically recommend using a temporary
> table, which is automatically removed after a session terminates.

Thanks Tim, I'm having some problems with memory tables and I'll
probably get back here one of these days when I'll hit that wall again.
It's not so straightforward like it was with DBISAM and I'm trying to
get used to it.

One question comes to mind though...

If I have a SQL this in an application and among other things

CREATE TABLE "mytemptable" AS SELECT * FROM somequeryetc

than mytemptable is used to populate some other SELECT ... which than
gets displayed in some grid

AND, the application is run by many people at the same time, how does
that work?

PLUS I hate edb dropped that DROP TABLE IF EXISTS ...


regards,
Lucian

Lucian


What I do is when a new user is created for the app I create a user in ElevateDB as well - same ID, the password is stored in ElevateDB not in my users table.

Roy Lambert [Team Elevate]

Just a reminder - temporary tables are process/session specific and would not be available to other users of the app.

Roy Lambert [Team Elevate]