Triggers ! 
Author Message
 Triggers !

Hi, I will also RTFM, but I have an urgent deadline ahead of me, and don't
know if the following is possible.

I have worked with simple triggers (insert / update / delete) before, and
these are not a problem.

What I need to do now is, a record is inserted into a table (with a
trigger),
I must then select matching data from a 2nd table, merge both records
and insert the result into a 3rd table (all using a trigger). To make things
a little more difficult, the select from the 2nd table can bring back
multiple rows.

I might be wrong, but I guess I'll have to use a trigger that calls an SPL
that will then do all the selects and validations I want into variables,
and insert the result into the target table.

If a stored procedure can use scrolling cursors, variables, etc.
(I haven't worked with SPL's before - please don't shoot me)

Dirk



Sun, 06 Jun 2004 22:05:20 GMT
 Triggers !

Hi,

See comments below:

Quote:

> Hi, I will also RTFM, but I have an urgent deadline ahead of me, and don't
> know if the following is possible.

> I have worked with simple triggers (insert / update / delete) before, and
> these are not a problem.

> What I need to do now is, a record is inserted into a table (with a
> trigger),
> I must then select matching data from a 2nd table, merge both records
> and insert the result into a 3rd table (all using a trigger). To make things
> a little more difficult, the select from the 2nd table can bring back
> multiple rows.

> I might be wrong, but I guess I'll have to use a trigger that calls an SPL
> that will then do all the selects and validations I want into variables,
> and insert the result into the target table.

SPL can do all of this.

Quote:

> If a stored procedure can use scrolling cursors, variables, etc.
> (I haven't worked with SPL's before - please don't shoot me)

Syntax is a bit funny, when compared to 4GL, but it all works.
There is a limit on the size of the SPL (64 k), but that's a lot of code.
If you want a working example, e-mail me.

Quote:

> Dirk

HTH

Michael



Mon, 07 Jun 2004 00:36:10 GMT
 Triggers !

Quote:
----- Original Message -----


Sent: Wednesday, December 19, 2001 5:05 PM
Subject: Triggers !

> Hi, I will also RTFM, but I have an urgent deadline ahead of me, and don't
> know if the following is possible.

> I have worked with simple triggers (insert / update / delete) before, and
> these are not a problem.

> What I need to do now is, a record is inserted into a table (with a
> trigger),
> I must then select matching data from a 2nd table, merge both records
> and insert the result into a 3rd table (all using a trigger). To make
things
> a little more difficult, the select from the 2nd table can bring back
> multiple rows.

> I might be wrong, but I guess I'll have to use a trigger that calls an SPL
> that will then do all the selects and validations I want into variables,
> and insert the result into the target table.

Well, you're right.

Quote:

> If a stored procedure can use scrolling cursors, variables, etc.
> (I haven't worked with SPL's before - please don't shoot me)

Puh ... puh ... puh ...

Hope  I'm not missing ...

- Show quoted text -

Quote:

> Dirk



Mon, 07 Jun 2004 00:23:58 GMT
 Triggers !
Quote:

> I might be wrong, but I guess I'll have to use a trigger that calls an SPL
> that will then do all the selects and validations I want into variables,
> and insert the result into the target table.

> If a stored procedure can use scrolling cursors, variables, etc.
> (I haven't worked with SPL's before - please don't shoot me)

Commander Data: "Lock 'n' Load"

Yup - only calling an SPL from the trigger will do the job for you. A
major shortcoming of standard SPL is that it doesn't have any dynamic
SQL - ie prepare/declare is not available but people say V9 has UDR
(or something) which can be much smarter. Must try that one day.

The cursors in standard SPL are embedded in the foreach statement.
Think of SPL as a restricted version of 4GL with an obsession for
correct use of the semicolon and you'll see your way through.

A stored procedure can also return multiple times (ie return multiple
rows) by using a "return a, b, c with resume" statement. They can be
driven from a foreach loop (or a 4GL open fetch fetch fetch ....
sequence).

Summary: no worries mate you can do what you want to with SPL.



Mon, 07 Jun 2004 06:39:27 GMT
 Triggers !


I appreciate the quick & detailed responses. I'll work on (and
hopefully finish) the SP today. Just have to get into the
syntax asap.

By the way, SPL is better supported in dbaccess right (rather than
isql) ?

Dirk

Quote:
-----Original Message-----

Sent: 20 December 2001 00:39

Subject: Re: Triggers !


> I might be wrong, but I guess I'll have to use a trigger that calls an
> SPL that will then do all the selects and validations I want into
> variables, and insert the result into the target table.

> If a stored procedure can use scrolling cursors, variables, etc. (I
> haven't worked with SPL's before - please don't shoot me)

Commander Data: "Lock 'n' Load"

Yup - only calling an SPL from the trigger will do the job for you. A major
shortcoming of standard SPL is that it doesn't have any dynamic SQL - ie
prepare/declare is not available but people say V9 has UDR (or something)
which can be much smarter. Must try that one day.

The cursors in standard SPL are embedded in the foreach statement. Think of
SPL as a restricted version of 4GL with an obsession for correct use of the
semicolon and you'll see your way through.

A stored procedure can also return multiple times (ie return multiple
rows) by using a "return a, b, c with resume" statement. They can be driven
from a foreach loop (or a 4GL open fetch fetch fetch .... sequence).

Summary: no worries mate you can do what you want to with SPL.



Mon, 07 Jun 2004 14:43:15 GMT
 Triggers !
Quote:



Yup - I've got that accounf for when I travel I can't hook into my Outlook
mail account. Actually I'm told I can thru the web, but I'd have to shutdown
my desktop Outlook, which is performing the essential service of absorbing
all that spam for fun and profit.

Quote:
>By the way, SPL is better supported in dbaccess right (rather
>than isql) ?

Yup - apparently isql splits statements at semicolon and sends them to the
engine, so that's death to any stored procedure which *must* have 100%
correct semicoloning before it's accepted.


Mon, 07 Jun 2004 16:41:19 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Triggers, Triggers and more Triggers

2. To trigger or not to trigger

3. Why aren't my Triggers triggering?

4. Disable Trigger within a Trigger

5. Update Trigger and ChildID Insert Trigger

6. SQL 2000 Multiple Triggers/Trigger Order

7. trigger for db audit purposes - newbie to the world of triggers

8. Default value and Trigger question ( implement before trigger)

9. Trigger Problem (Delete Trigger)

10. Trigger Newbie... Help with Trigger

11. Disable a trigger from within another trigger or stored procedure

12. Trigger-Happy Triggers


 
Powered by phpBB® Forum Software