Playing with triggers ...

Hello,

I'm starting to use triggers.

What i need is to create a record in a table (let's say TABLE B) when a new
record in TABLE A is created.

This is easy with triggers:

DROP TRIGGER IF EXISTS "insert_tablea";

CREATE TRIGGER insert_tablea
BEFORE INSERT ON tablea
REFERENCING OLD AS o NEW AS n
BEGIN
insert into tableb (field1,field2,field3) values
(n.field1,n.field2,n.field3);
END


The problem is that in TABLE B i need also to set a field with the ID of the
current user logged in my application .... (i have such field in all tables)

This information is stored into a global variable in my application of
course.

I have no idea on how to do it ....

Roberto

Roberto,

Wouldn't that make it then? If the field is in tableA then just copy it
to TableB?

Another way I would think of is to have the logged in user stored in a
##-table that is unique to your session. The trigger could access that
table. Haven't tried anything like it, though...

/Anders


--



Roberto Nicchi wrote:

> (i have such field in all tables)

The user id field is now set in the onbeforeprint event of a TnxTable so in
the insert trigger the value is not available (trigger is executed after the
onbeforepost)

the temporary session table is the solution i think. i'll try it and i bet
it will work. thanks

Roberto

"Anders Gustavsson" <s.anders.gustavsson@telia.com> ha scritto nel messaggio
news:493a8481$1@77-37-8-25....
> Roberto,
>
> Wouldn't that make it then? If the field is in tableA then just copy it
> to TableB?
>
> Another way I would think of is to have the logged in user stored in a
> ##-table that is unique to your session. The trigger could access that
> table. Haven't tried anything like it, though...
>
> /Anders
>
>
> --
>
>
>
> Roberto Nicchi wrote:
>
>> (i have such field in all tables)

ehm, reading again what i just wrote i can't belive i wrote it ...
I have to stop to think to work on weekend ...

anyway i like the idea of the temporary session table ...

Roberto

"Roberto Nicchi" <software@masterinformatica.net> ha scritto nel messaggio
news:493a9c90$1@77-37-8-25....
> The user id field is now set in the onbeforeprint event of a TnxTable so
> in the insert trigger the value is not available (trigger is executed
> after the onbeforepost)

Roberto Nicchi wrote:
> Hello,

> The problem is that in TABLE B i need also to set a field with the ID of
> the current user logged in my application .... (i have such field in all
> tables)
>
> This information is stored into a global variable in my application of
> course.
>
> I have no idea on how to do it ....
>
> Roberto

With ##TABLES sticking around for the session I have used
the following stored procedure and function to get some
form of session variables. Not ideal but functional.


DROP ROUTINE IF EXISTS "SET_SESSION_VARIABLE";

CREATE PROCEDURE SET_SESSION_VARIABLE(VAR_NAME VARCHAR(40),VAR_VALUE
VARCHAR(40))
MODIFIES SQL DATA
BEGIN
TRY
UPDATE ##SESSION_VARIABLES SET VARIABLE_VALUE = VAR_VALUE
WHERE VARIABLE_NAME = VAR_NAME;
IF ROWSAFFECTED = 0 THEN
INSERT INTO ##SESSION_VARIABLES VALUES(VAR_NAME,VAR_VALUE);
END IF;
CATCH POSITION('Unable to open table' IN ERROR_MESSAGE) > 0
CREATE TABLE ##SESSION_VARIABLES
(VARIABLE_NAME ShortString(40),VARIABLE_VALUE ShortString(40));
CREATE INDEX Index_VARNAME ON ##SESSION_VARIABLES(VARIABLE_NAME);
CALL SET_SESSION_VARIABLE(VAR_NAME,VAR_VALUE);
END;
END;

DROP ROUTINE IF EXISTS GET_SESSION_VARIABLE;

CREATE FUNCTION GET_SESSION_VARIABLE(VAR_NAME VARCHAR(40))
RETURNS VARCHAR(40)
READS SQL DATA
BEGIN
DECLARE RET_VAR VARCHAR(40);
TRY
SET RET_VAR = (SELECT VARIABLE_VALUE FROM ##SESSION_VARIABLES
WHERE VARIABLE_NAME = VAR_NAME);
CATCH POSITION('Unable to open table' IN ERROR_MESSAGE) > 0
SET RET_VAR = null;
END;
RETURN RET_VAR;
END;

-----
// Developer does this at start of user session

CALL SET_SESSION_VARIABLE('SUBST_USER','Dave');

..
..



--
Brian Evans [NDX]
Ottawa, ON, CANADA
GMT-5

nice, thanks Brian!

"Brian Evans [NDX]" <bevanson@rogers.com> ha scritto nel messaggio
news:493ac980@77-37-8-25....
> Roberto Nicchi wrote:
>> Hello,
>
>> The problem is that in TABLE B i need also to set a field with the ID of
>> the current user logged in my application .... (i have such field in all
>> tables)
>>
>> This information is stored into a global variable in my application of
>> course.
>>
>> I have no idea on how to do it ....
>>
>> Roberto
>
> With ##TABLES sticking around for the session I have used
> the following stored procedure and function to get some
> form of session variables. Not ideal but functional.
>
>
> DROP ROUTINE IF EXISTS "SET_SESSION_VARIABLE";
>
> CREATE PROCEDURE SET_SESSION_VARIABLE(VAR_NAME VARCHAR(40),VAR_VALUE
> VARCHAR(40))
> MODIFIES SQL DATA
> BEGIN
> TRY
> UPDATE ##SESSION_VARIABLES SET VARIABLE_VALUE = VAR_VALUE
> WHERE VARIABLE_NAME = VAR_NAME;
> IF ROWSAFFECTED = 0 THEN
> INSERT INTO ##SESSION_VARIABLES VALUES(VAR_NAME,VAR_VALUE);
> END IF;
> CATCH POSITION('Unable to open table' IN ERROR_MESSAGE) > 0
> CREATE TABLE ##SESSION_VARIABLES
> (VARIABLE_NAME ShortString(40),VARIABLE_VALUE ShortString(40));
> CREATE INDEX Index_VARNAME ON ##SESSION_VARIABLES(VARIABLE_NAME);
> CALL SET_SESSION_VARIABLE(VAR_NAME,VAR_VALUE);
> END;
> END;
>
> DROP ROUTINE IF EXISTS GET_SESSION_VARIABLE;
>
> CREATE FUNCTION GET_SESSION_VARIABLE(VAR_NAME VARCHAR(40))
> RETURNS VARCHAR(40)
> READS SQL DATA
> BEGIN
> DECLARE RET_VAR VARCHAR(40);
> TRY
> SET RET_VAR = (SELECT VARIABLE_VALUE FROM ##SESSION_VARIABLES
> WHERE VARIABLE_NAME = VAR_NAME);
> CATCH POSITION('Unable to open table' IN ERROR_MESSAGE) > 0
> SET RET_VAR = null;
> END;
> RETURN RET_VAR;
> END;
>
> -----
> // Developer does this at start of user session
>
> CALL SET_SESSION_VARIABLE('SUBST_USER','Dave');
>
> .
> .
>
>
>
> --
> Brian Evans [NDX]
> Ottawa, ON, CANADA
> GMT-5