Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

sp_GetApplicationID() in trigger

8 posts in Trigger Last posting was on 2006-05-16 12:54:51.0Z
Thomas Steinmaurer Posted on 2006-04-09 10:53:53.0Z
Date: Sun, 09 Apr 2006 12:53:53 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam_@upscene.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: sp_GetApplicationID() in trigger
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 86.56.177.91
Message-ID: <4438e69a@solutions.advantagedatabase.com>
X-Trace: 9 Apr 2006 04:48:58 -0700, 86.56.177.91
Lines: 30
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!86.56.177.91
Xref: solutions.advantagedatabase.com Advantage.Trigger:210
Article PK: 1136275

Hello,

I would like to use the new sp_GetApplicationID() stored procedure for
enhancing our auditing mechanism, but I do not know how to use that in a
trigger. Basically, what I want to do is, storing the return value in a
log table.

I've found several examples in the online help regarding storing a
return value in a local variable when using SELECT ... FROM ..., for
instance:

DECLARE i Integer;
i = ( SELECT id FROM #input );

but I didn't find anything similar when executing a stored procedure
with EXECUTE PROCEDURE.


Any hints?


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com


Joachim Duerr (ADS Support) Posted on 2006-04-09 19:19:01.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: sp_GetApplicationID() in trigger
Newsgroups: Advantage.Trigger
References: <4438e69a@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 84.158.172.91
Message-ID: <44395015@solutions.advantagedatabase.com>
Date: 9 Apr 2006 12:19:01 -0700
X-Trace: 9 Apr 2006 12:19:01 -0700, 84.158.172.91
Lines: 46
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!84.158.172.91
Xref: solutions.advantagedatabase.com Advantage.Trigger:211
Article PK: 1136277


Thomas Steinmaurer wrote in <4438e69a@solutions.advantagedatabase.com>:

> I would like to use the new sp_GetApplicationID() stored procedure
> for enhancing our auditing mechanism, but I do not know how to use
> that in a trigger. Basically, what I want to do is, storing the
> return value in a log table.
>
> I've found several examples in the online help regarding storing a
> return value in a local variable when using SELECT ... FROM ..., for
> instance:
>
> DECLARE i Integer;
> i = ( SELECT id FROM #input );
>
> but I didn't find anything similar when executing a stored procedure
> with EXECUTE PROCEDURE.
>
>
> Any hints?

CREATE TRIGGER trig_ins_test
ON test
INSTEAD OF INSERT
BEGIN
DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
OPEN c;
FETCH c;
UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
UPDATE __new SET lastapp=c.ApplicationID;
INSERT INTO test SELECT * FROM __new;
END;

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de
*********** European Advantage Conference (EAC) 2006 ****************
Do you want to develop faster, deliver better solutions and drive your
business forward?
Plan to attend now THE Advantage event of the Year!
+++ UK - London, 8.-9. May
+++ GERMANY - Frankfurt, 11.-12. May
+++ ITALY - Milano, 15. May
Click for Details: http://www.AdvantageDatabase.com/EAC2006
*********** European Advantage Conference (EAC) 2006 ****************


Thomas Steinmaurer Posted on 2006-04-09 19:22:30.0Z
Date: Sun, 09 Apr 2006 21:22:30 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam_@upscene.com>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: sp_GetApplicationID() in trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com>
In-Reply-To: <44395015@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 86.56.177.91
Message-ID: <44395dcc@solutions.advantagedatabase.com>
X-Trace: 9 Apr 2006 13:17:32 -0700, 86.56.177.91
Lines: 49
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!86.56.177.91
Xref: solutions.advantagedatabase.com Advantage.Trigger:212
Article PK: 1136280


> Thomas Steinmaurer wrote in <4438e69a@solutions.advantagedatabase.com>:
>
>
>>I would like to use the new sp_GetApplicationID() stored procedure
>>for enhancing our auditing mechanism, but I do not know how to use
>>that in a trigger. Basically, what I want to do is, storing the
>>return value in a log table.
>>
>>I've found several examples in the online help regarding storing a
>>return value in a local variable when using SELECT ... FROM ..., for
>>instance:
>>
>>DECLARE i Integer;
>>i = ( SELECT id FROM #input );
>>
>>but I didn't find anything similar when executing a stored procedure
>>with EXECUTE PROCEDURE.
>>
>>
>>Any hints?
>
>
> CREATE TRIGGER trig_ins_test
> ON test
> INSTEAD OF INSERT
> BEGIN
> DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
> OPEN c;
> FETCH c;
> UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
> UPDATE __new SET lastapp=c.ApplicationID;
> INSERT INTO test SELECT * FROM __new;
> END;

Joachim, thanks a lot. Works like a treat! Didn't know that I've to do
that via an explicit cursor.

Thanks!


--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com


Joachim Duerr (ADS Support) Posted on 2006-04-10 08:02:37.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: sp_GetApplicationID() in trigger
Newsgroups: Advantage.Trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com> <44395dcc@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 195.2.185.25
Message-ID: <443a030d@solutions.advantagedatabase.com>
Date: 10 Apr 2006 01:02:37 -0700
X-Trace: 10 Apr 2006 01:02:37 -0700, 195.2.185.25
Lines: 36
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!195.2.185.25
Xref: solutions.advantagedatabase.com Advantage.Trigger:213
Article PK: 1136278


Thomas Steinmaurer wrote in <44395dcc@solutions.advantagedatabase.com>:

> Joachim, thanks a lot. Works like a treat! Didn't know that I've to
> do that via an explicit cursor.

forgot one thing: don't forget to close the cursor afterwards. It
should cause no problem to leave it open but I don't know if it'll
consume resources:

CREATE TRIGGER trig_ins_test
ON test
INSTEAD OF INSERT
BEGIN
DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
OPEN c;
FETCH c;
UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
UPDATE __new SET lastapp=c.ApplicationID;
INSERT INTO test SELECT * FROM __new;
CLOSE c; -- <<<<<<<<<<<<<<<
END;

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de
*********** European Advantage Conference (EAC) 2006 ****************
Do you want to develop faster, deliver better solutions and drive your
business forward?
Plan to attend now THE Advantage event of the Year!
+++ UK - London, 8.-9. May
+++ GERMANY - Frankfurt, 11.-12. May
+++ ITALY - Milano, 15. May
Click for Details: http://www.AdvantageDatabase.com/EAC2006
*********** European Advantage Conference (EAC) 2006 ****************


Thomas Steinmaurer Posted on 2006-04-10 07:25:23.0Z
Date: Mon, 10 Apr 2006 09:25:23 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam@upscene.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: sp_GetApplicationID() in trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com> <44395dcc@solutions.advantagedatabase.com> <443a030d@solutions.advantagedatabase.com>
In-Reply-To: <443a030d@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 193.186.173.108
Message-ID: <443a0799@solutions.advantagedatabase.com>
X-Trace: 10 Apr 2006 01:22:01 -0700, 193.186.173.108
Lines: 40
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!193.186.173.108
Xref: solutions.advantagedatabase.com Advantage.Trigger:214
Article PK: 1136281


> Thomas Steinmaurer wrote in <44395dcc@solutions.advantagedatabase.com>:
>
>> Joachim, thanks a lot. Works like a treat! Didn't know that I've to
>> do that via an explicit cursor.
>
> forgot one thing: don't forget to close the cursor afterwards. It
> should cause no problem to leave it open but I don't know if it'll
> consume resources:
>
> CREATE TRIGGER trig_ins_test
> ON test
> INSTEAD OF INSERT
> BEGIN
> DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
> OPEN c;
> FETCH c;
> UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
> UPDATE __new SET lastapp=c.ApplicationID;
> INSERT INTO test SELECT * FROM __new;
> CLOSE c; -- <<<<<<<<<<<<<<<
> END;

Yup. Thanks. And, to be on the safe side, one should do that in a TRY
... FINALLY block.


DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
OPEN c;
TRY
FETCH c;
UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
UPDATE __new SET lastapp=c.ApplicationID;
INSERT INTO test SELECT * FROM __new;
FINALLY
CLOSE c;
END TRY;


Regards,
Thomas


Alex Wong (ADS) Posted on 2006-05-12 23:39:58.0Z
From: "Alex Wong \(ADS\)" <alexw@sybase.com>
Newsgroups: Advantage.Trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com> <44395dcc@solutions.advantagedatabase.com> <443a030d@solutions.advantagedatabase.com> <443a0799@solutions.advantagedatabase.com>
Subject: Re: sp_GetApplicationID() in trigger
Date: Fri, 12 May 2006 17:39:58 -0600
Lines: 33
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
X-RFC2646: Format=Flowed; Response
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2869
NNTP-Posting-Host: 10.24.38.166
Message-ID: <44651a92@solutions.advantagedatabase.com>
X-Trace: 12 May 2006 17:30:26 -0700, 10.24.38.166
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!10.24.38.166
Xref: solutions.advantagedatabase.com Advantage.Trigger:229
Article PK: 1136296


"Thomas Steinmaurer" <t.steinmaurer_dontbugmewithspam@upscene.com> wrote in
message news:443a0799@solutions.advantagedatabase.com...
>> Thomas Steinmaurer wrote in <44395dcc@solutions.advantagedatabase.com>:
>>
>
> Yup. Thanks. And, to be on the safe side, one should do that in a TRY ...
> FINALLY block.
>
>
> DECLARE c CURSOR AS EXECUTE PROCEDURE sp_GetApplicationID();
> OPEN c;
> TRY
> FETCH c;
> UPDATE __new SET lastuser=(SELECT USER() FROM SYSTEM.IOTA);
> UPDATE __new SET lastapp=c.ApplicationID;
> INSERT INTO test SELECT * FROM __new;
> FINALLY
> CLOSE c;
> END TRY;
>

USER() is a scalar function so there is no need to select it from the iota
table.

UPDATE __new SET lastuser= USER();

It is more efficient.

Alex


Joachim Duerr (ADS Support) Posted on 2006-05-13 02:09:13.0Z
From: "Joachim Duerr (ADS Support)" <jojo.duerr@gmx.de>
Subject: Re: sp_GetApplicationID() in trigger
Newsgroups: Advantage.Trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com> <44395dcc@solutions.advantagedatabase.com> <443a030d@solutions.advantagedatabase.com> <443a0799@solutions.advantagedatabase.com> <44651a92@solutions.advantagedatabase.com>
Organization: iAnywhere
User-Agent: XanaNews/1.18.1.2
X-Face: ,QMv7[luB)BpWAQ~:"kw6n%0ieY63.:g2K3n~8ky0;||5Xle*Xq+=~<Fy:0CVC2nx@8~vZ
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
NNTP-Posting-Host: 83.236.138.242
Message-ID: <446531b9@solutions.advantagedatabase.com>
Date: 12 May 2006 19:09:13 -0700
X-Trace: 12 May 2006 19:09:13 -0700, 83.236.138.242
Lines: 27
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!83.236.138.242
Xref: solutions.advantagedatabase.com Advantage.Trigger:230
Article PK: 1136295


Alex Wong (ADS) wrote in <44651a92@solutions.advantagedatabase.com>:

> USER() is a scalar function so there is no need to select it from the
> iota table.
>
> UPDATE __new SET lastuser= USER();
>
> It is more efficient.

another enhancement: in the meantime I found out that ApplicationId()
is also available as a scalar function and so you don't need to open a
cursor with the EXECUTE PROCEDURE statement...

--
Joachim Duerr
Senior Product Support Analyst (Advantage Database Server)
iAnywhere Solutions / Extended Systems
advantage[AT]extendsys.de
*********** European Advantage Conference (EAC) 2006 ****************
Do you want to develop faster, deliver better solutions and drive your
business forward?
Plan to attend now THE Advantage event of the Year!
+++ UK - London, 8.-9. May
+++ GERMANY - Frankfurt, 11.-12. May
+++ ITALY - Milano, 15. May
Click for Details: http://www.AdvantageDatabase.com/EAC2006
*********** European Advantage Conference (EAC) 2006 ****************


Thomas Steinmaurer Posted on 2006-05-16 12:54:51.0Z
Date: Tue, 16 May 2006 14:54:51 +0200
From: Thomas Steinmaurer <t.steinmaurer_dontbugmewithspam@upscene.com>
User-Agent: Thunderbird 1.5.0.2 (Windows/20060308)
MIME-Version: 1.0
Newsgroups: Advantage.Trigger
Subject: Re: sp_GetApplicationID() in trigger
References: <4438e69a@solutions.advantagedatabase.com> <44395015@solutions.advantagedatabase.com> <44395dcc@solutions.advantagedatabase.com> <443a030d@solutions.advantagedatabase.com> <443a0799@solutions.advantagedatabase.com> <44651a92@solutions.advantagedatabase.com> <446531b9@solutions.advantagedatabase.com>
In-Reply-To: <446531b9@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 193.186.173.108
Message-ID: <4469cac9@solutions.advantagedatabase.com>
X-Trace: 16 May 2006 06:51:21 -0700, 193.186.173.108
Lines: 25
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!193.186.173.108
Xref: solutions.advantagedatabase.com Advantage.Trigger:231
Article PK: 1136297

Joachim,

>> USER() is a scalar function so there is no need to select it from the
>> iota table.
>>
>> UPDATE __new SET lastuser= USER();
>>
>> It is more efficient.
>
> another enhancement: in the meantime I found out that ApplicationId()
> is also available as a scalar function and so you don't need to open a
> cursor with the EXECUTE PROCEDURE statement...

Good catch. Thanks for letting us now! ;-)



--
Best Regards,
Thomas Steinmaurer
LogManager Series - Logging/Auditing Suites supporting
InterBase, Firebird, Advantage Database, MS SQL Server and
NexusDB V2
Upscene Productions
http://www.upscene.com