ADS Triggers

[Marcelo Via Giglio]

Hello,

have some body experiences with ADS triggers?, I am trying to develop a system loger, with trigger can be really easy, but the problem
is how can I send extra parameter to a trigger, is possible to obtain the date and the time to save the history data, but I don't know how can I send
the user information.

regards

Marcelo

[reinaldocrespo]

Hi.

I use ADS triggers extensively.

ADS triggers can receive __new and __old cursors containing the new and the old data. For time and date, you wouldn't need to send anything. You can always use the ADS SQL engine function now(). As in UPDATE table SET dt_field = now(). If the table is ADT or DBF /CDX, then you could have a field type datetime that defaults to now() - then you wouldn't even have to worry about ever writing to that field.

Look at this trigger as a sample. It has many things:

Code: Select all  Expand view

    declare @is_email LOGICAL;
    declare @email CHAR(100);

    declare @is_hosp_email LOGICAL;
    declare @hosp_email CHAR(100);
   
    declare @old cursor as select * from __old;
    declare @new cursor as select * from __new;

    open @old;
    fetch @old;
    open @new;
    fetch @new;
         
    if @new.signed = TRUE AND @old.signed = FALSE then
 
        @is_email = ( SELECT is_email from doctors where doctors.id = @new.refer_id );
        @email = ( SELECT email from doctors where doctors.id = @new.refer_id );

        if @is_email = TRUE AND @email IS NOT NULL then
         
            INSERT INTO plmail ( pathno, doc_id, send_to )
                   VALUES ( @new.pathno, @new.refer_id,
                          ( SELECT email FROM doctors
                            WHERE doctors.id = @new.refer_id
                            AND doctors.is_email = TRUE
                            AND doctors.email IS NOT NULL) );
       
            INSERT INTO plmail ( pathno, doc_id, send_to )
                   SELECT c.pathno, c.copyto, doc.email FROM copyto c
                      LEFT JOIN doctors doc ON doc.id = c.copyto
                      WHERE c.pathno = @new.pathno
                      AND doc.is_email = TRUE
                      AND doc.email IS NOT NULL ;
 
         endif ;

        if @new.facility IS NOT NULL then
            @is_hosp_email = ( SELECT TOP 1 is_email from hospital where hospital.name = @new.facility );
            @hosp_email = ( SELECT TOP 1 e_mail from hospital where hospital.name = @new.facility );

            if @is_hosp_email = TRUE AND @hosp_email IS NOT NULL then  
                INSERT INTO plmail ( pathno, doc_id, send_to )
                    SELECT @new.pathno, h.passwd, h.e_mail from hospital h
                        WHERE h.name = @new.facility ;
            endif;
        endif;
    endif;

    if @new.signed = FALSE AND @old.signed = TRUE then
         DELETE FROM plmail
                WHERE pathno = @new.pathno
                AND DateTime_Sent IS NULL ;

         DELETE FROM pthreps
                WHERE pathno = @new.pathno;

     endif ;
 

[Marcelo Via Giglio]

Reinaldo,

thanks you very much for your sample, the date and time is possible obtain across ADS function, but I need to save the user name who is lunch the operation, are there the possibility to send an parameter, like user name?

regards

Marcelo

[reinaldocrespo]

No that I know of.

However, after connecting to the DD you could change property appid to the username or to username+_+appid. With that, you can always query appid() and parse the result to extract the user name. Another good thing about doing this, is that now you also have the user name displayed on one of the columns on the "remote info" option of arc32.

Look for sp_SetApplicationID() and sp_GetApplicationID() on the help file.

Hope that helps,


Reinaldo.