Trigger error 
Author Message
 Trigger error

I have just created a simple procedure just to test insert / update
statements from within a procedure, but the procedure keeps failing
with the error:

-675    Illegal SQL statement in SPL routine.

I can run the SP with or without the line "RETURN mval",
and it keeps giving the same error above. If I take
the insert out, it works fine.

The strange thing is that the manual gives an example of
an insert statement inside a procedure, but the example
also doesn't work.
Any ideas ?

** I am using IDS7.31 UC7 on Solaris 7
Here is the procedure I created:

CREATE PROCEDURE dirk(in_param  char(10))
RETURNING char(1);

define mval  char(1);

LET mval = in_param[5,5];

INSERT INTO dirk1 VALUES(mval);
--RETURN mval

END PROCEDURE;

Dirk



Mon, 07 Jun 2004 16:08:43 GMT
 Trigger error

Quote:

>The strange thing is that the manual gives an example of
>an insert statement inside a procedure, but the example
>also doesn't work.

i believe it's illegal for and SPL to work on the same table as the trigger
it's called from

--
Space Corps Directive #997
Work done by an officer's doppleganger in a parallel
universe cannot be claimed as overtime.
    -- Red Dwarf
..  ... .--. .. -  --- -.  --- .-. .- -.-. .-.. .



Mon, 07 Jun 2004 16:44:05 GMT
 Trigger error

Dirk,

You procedure can't modify database state ( insert, delete, update )
if it's called from SQL statements.

for example you can't do

select dirk() from same_table;
select ... from ... where x = dirk()

update ... set x = dirk() where ...

and so on.

( Just like Oracle's pragma restict )

Just 'execute procedure' syntax.

Good luck.

sve

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


Sent: Thursday, December 20, 2001 11:08 AM
Subject: Trigger error

> I have just created a simple procedure just to test insert / update
> statements from within a procedure, but the procedure keeps failing
> with the error:

> -675    Illegal SQL statement in SPL routine.

> I can run the SP with or without the line "RETURN mval",
> and it keeps giving the same error above. If I take
> the insert out, it works fine.

> The strange thing is that the manual gives an example of
> an insert statement inside a procedure, but the example
> also doesn't work.
> Any ideas ?

> ** I am using IDS7.31 UC7 on Solaris 7
> Here is the procedure I created:

> CREATE PROCEDURE dirk(in_param  char(10))
> RETURNING char(1);

> define mval  char(1);

> LET mval = in_param[5,5];

> INSERT INTO dirk1 VALUES(mval);
> --RETURN mval

> END PROCEDURE;

> Dirk



Mon, 07 Jun 2004 17:58:45 GMT
 Trigger error
Quote:

>Dirk,

>You procedure can't modify database state ( insert, delete, update )
>if it's called from SQL statements.

Emmm, that's not true. I think it was an old rule, but it's been relaxed. I
know personally of some procs fired from triggers which modify other tables
;-)

but there's a rule against modifying the same table from it's own triggers
which is probably what Dirk is tripping over.



Tue, 08 Jun 2004 06:18:46 GMT
 Trigger error

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


Sent: Friday, December 21, 2001 1:18 AM
Subject: Re: Trigger error


> >Dirk,

> >You procedure can't modify database state ( insert, delete, update )
> >if it's called from SQL statements.

> Emmm, that's not true. I think it was an old rule, but it's been relaxed.
I
> know personally of some procs fired from triggers which modify other

tables
                                                   ~~~~~~~~~~~~
!!! fired from triggers !!!

That's important note. I told about SQL statements.

That's truth.

- Show quoted text -

Quote:
> ;-)

> but there's a rule against modifying the same table from it's own triggers
> which is probably what Dirk is tripping over.



Tue, 08 Jun 2004 16:35:44 GMT
 Trigger error

Not sure who is right here. I took the same syntax, and then executed the
procedure like Sergey suggested, and it worked ok. The first time I tested
it, I called the procedure from a select statement -
"select procname() from table where ....", which didn't work.

When you say "relaxed", which version/s are you talking about? We are using
IDS7.31 here.   Maybe IDS2000 ?

I am close to finalising the trigger / SP. I created an update trigger, and
all it does is to execute a procedure. Inside the SP I do everything I
want, selecting data, doing checks, and finally inserting or updating
other tables.

Thanks for all the help everyone. In one day, between the list and the
manuals, I certainly learnt a lot.

Dirk

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

Sent: 21 December 2001 00:19

Subject: Re: Trigger error


>Dirk,

>You procedure can't modify database state ( insert, delete, update ) if
>it's called from SQL statements.

Emmm, that's not true. I think it was an old rule, but it's been relaxed. I
know personally of some procs fired from triggers which modify other tables
;-)

but there's a rule against modifying the same table from it's own triggers
which is probably what Dirk is tripping over.



Tue, 08 Jun 2004 16:59:53 GMT
 Trigger error

When I read this it made me smile. I just sent an e-mail saying I don't know
which one of you are right - it turns out, you both are  :-)  
You were just talking about 2 different situations.

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

----- Original Message -----


> >Dirk,

> >You procedure can't modify database state ( insert, delete, update )
> >if it's called from SQL statements.

> Emmm, that's not true. I think it was an old rule, but it's been
> relaxed.
I
> know personally of some procs fired from triggers which modify other

tables
                                                   ~~~~~~~~~~~~ !!! fired
from triggers !!!

That's important note. I told about SQL statements.

That's truth.

- Show quoted text -

Quote:
> ;-)

> but there's a rule against modifying the same table from it's own
> triggers which is probably what Dirk is tripping over.



Tue, 08 Jun 2004 17:42:24 GMT
 Trigger error

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


Sent: Friday, December 21, 2001 11:59 AM
Subject: RE: Trigger error

> Not sure who is right here. I took the same syntax, and then executed the
> procedure like Sergey suggested, and it worked ok. The first time I tested
> it, I called the procedure from a select statement -
> "select procname() from table where ....", which didn't work.

> When you say "relaxed", which version/s are you talking about? We are
using
> IDS7.31 here.   Maybe IDS2000 ?

No it's true even for IDS2000.
But you're right, Andrew and me was speaking about two different things.

Quote:

> I am close to finalising the trigger / SP. I created an update trigger,
and
> all it does is to execute a procedure. Inside the SP I do everything I
> want, selecting data, doing checks, and finally inserting or updating
> other tables.

> Thanks for all the help everyone. In one day, between the list and the
> manuals, I certainly learnt a lot.

> Dirk

> -----Original Message-----

> Sent: 21 December 2001 00:19

> Subject: Re: Trigger error


> >Dirk,

> >You procedure can't modify database state ( insert, delete, update ) if
> >it's called from SQL statements.

> Emmm, that's not true. I think it was an old rule, but it's been relaxed.
I
> know personally of some procs fired from triggers which modify other
tables
> ;-)

> but there's a rule against modifying the same table from it's own triggers
> which is probably what Dirk is tripping over.



Tue, 08 Jun 2004 19:44:35 GMT
 
 [ 8 post ] 

 Relevant Pages 

1. Help: Invalid Trigger Error on Valid trigger

2. Trigger error message

3. trigger error - Invalid Object "Inserted"

4. Trigger error with ADO

5. Returning Trigger Error Descriptions to Access 97

6. trigger error...Connection is busy with results for another hstmt

7. Trigger error using TEXT data type columns

8. Trigger error when inserting TEXT or image column values into a mirror table

9. Catching Trigger Errors from dynamic SQL exec

10. trigger error 107

11. Update Trigger Error

12. Triggers - Error Handling...


 
Powered by phpBB® Forum Software