Calling a sproc for each affected record from within trigger in Sybase ASA 6

Question

just having some headaches implementing something in a trigger on a old version of Sybase and wondered if anyone could help. In a nutshell, I want to write a trigger that will call another sproc for each of the affected records (whether it be inserted, deleted or both [updated]).

One way to do this in T-SQL (SQL Server) would be to use a cursor but none of the cursor examples I've found on the web seem to actually work on Sybase ASA 6.0.4 - it either dislikes the T-SQL in general or it appears to compile but generally does nothing (even with pathologically simple, contrived scenarios).

Essentially I want to loop through all of the 'inserted' table records (if any) and for each one Exec MySproc (inserted.keyid), then loop through all of the 'deleted' table records (if any), and again for each one Exec MySproc (deleted.keyid).

Does anyone have an example of doing this sort of thing, or even just of using a read-only cursor in ASA 6? The online manual seems to have an example of a simple cursor but in practise I've not been able to make the example actually work. I could unroll the other sproc into the trigger (it's not that complicated really) but it's too complicated to be done as part of a 'select from inserted...' type statement - it really is a few lines of code in it's own right. Hence me thinking I need a cursor anyway.

EDIT: (29/12/09) - I ended up unrolling the sproc code, in desperation, but I really would like to have a working cursor example in Sybase ASA 6 because sooner or later I'll be faced with something I can't easily unroll. So I'll throw this up for bounty - can someone give me a working example? :-)

Why not just incorporate the logic into your insert sproc, so the sproc you're trying to implement gets called after the INSERT statement? — OMG Ponies, Dec 21 '09 at 07:03
@OMG Ponies - unfortunately I'm not in control of all the methods by which the table can be changed (there are other apps involved with this database as well as mine). We want to use a trigger to ensure that regardless of how the table is getting changed, the sproc will always get called and keep various other bits of the system 'up to date'. Up until now they've been manually running jobs at the end of the day but they wanted something a bit more 'real time'. :-) — robsoft, Dec 21 '09 at 09:50
Database is not performing at best if you're running these at run-time. Also using cursors in triggers are not things you want. If some of our devs or dba'ers want to create triggers with cursors init will be called back, because we don't allow cursors in triggers. It will cause alot of lag. — pipelinecache, Dec 29 '09 at 15:44
Why not show the code that did not work and explain what did happen when it ran? Someone may be able to point out the error. Short of that, I doubt many people (if anyone) will attempt to write a Sybase ASA 6.0.4 cursor loop from scratch for you. — KM., Dec 30 '09 at 15:57
Hi KM. I don't have any code that doesn't work, as such - it's more that every example I've found on the web simply doesn't work on ASA 6. I don't know if it's me (probably is!) but fundamentally the code examples don't even 'compile'. I'll go back and dig out some examples/links (from Sybase's website) and illustrate further. I'm not asking anyone to fix my problem (I don't really have one right now) - I just want to find someone who has done a cursor in this version of ASA, who can show me the correct incantation to make it compile. :-) — robsoft, Dec 30 '09 at 20:48

score 1 · Accepted Answer · answered Jan 02 '10 at 18:15

It sounds like you are looking for a ROW LEVEL trigger.

By default, triggers in ASA are statement level (the trigger executes once after the entire triggering statement is completed). Row level triggers, on the other hand, execute once for each row that is changed.

Here is an example of a row level trigger:

CREATE TRIGGER tr_log
AFTER UPDATE OF "myfield"
ON mytable
REFERENCING OLD AS old_data NEW AS new_data

FOR EACH ROW

BEGIN
    Insert into Narc_USER_INFO_Change (Field_Changed, New_Value, Original_Value, user)
    Values('myfield', new_data.myfield,  old_data.myfield, CURRENT USER);
END

Thanks Gabriel - I hadn't appreciated that I could write ROW LEVEL triggers instead of statement level ones. I'm more familiar with working at ROW LEVEL (from Interbase 6 triggers) and I'm glad that I can use this technique to avoid cursors. You get the bounty; your suggestion has worked on my database here and I'm happy to use this approach in future. Many thanks! — robsoft, Jan 04 '10 at 19:18

Calling a sproc for each affected record from within trigger in Sybase ASA 6

1 Answers1