I have to create a trigger that will fire when a row in a table is
inserted or updated, the trigger will copy the new row that is
inserted or deleted into a new table.. It sounds simple but I cannot
get the syntax correct - using sybase version 9.0.2. any help really
appreciated!

I have tried the following & it is not working:


ALTER TRIGGER "HoldingTrig".HoldingTrig after insert order 4 on
DBA.TBLRECORD
referencing new as new_name
for each statement
begin
declare @idd integer;
declare @idf integer;
declare @el1el integer;
declare @ids integer;
declare @err_notfound exception for sqlstate value '02000';
//declare a cursor for table new_name
declare new1 dynamic scroll cursor for select
ID_drd,id_fc,elog1_el,id_dss from
new_name;
open new1;
//Open the cursor, and get the value
LoopGetRow: loop
fetch next new1 into @iddrd,
@idfc,@elog1el,@iddss;
insert into tblholding_th( ID_drd,id_fc,elog1_el,id_dss) values
( @idd,@idf,@el1el,@ids) ;
if sqlstate = @err_notfound then
leave LoopGetRow
end if
end loop LoopGetRow;
close new1
end

What error or behaviour are you getting? What version and build of SQL
Anywhere are you running?

Glenn

Zoe wrote:
--
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at
http://www.sybase.com/products/datab...chnicalsupport

I am not getting an error - nothing is happening, when I insert a new
row into table 1 (TBLRECORD) the row should be copied and inserted
into table 2 (tblholding_th) but table2 is still empty. All column
names are correct.

What version and build of SQL Anywhere are you running?
I am using SQL version 9.0.2, build 3508

Not sure why your trigger is not working. The target table may need to be
qualified by its owner if that owner is not HoldingTrig; if that's the case, an
error would be reported.

Here's a modified version that is simpler. Note that you could also have chosen
to write this trigger as a row-level trigger (likely more efficient if rows are
being inserted into TBLRECORD one-at-a-time rather than with an INSERT ... SELECT).

If this still doesn't work, you might try adding MESSAGE ... TO CONSOLE
statements to the trigger to verify that it is being called.

ALTER TRIGGER "HoldingTrig".HoldingTrig after insert order 4 on
DBA.TBLRECORD
referencing new as new_name
for each statement
begin
insert into tblholding_th (ID_drd,id_fc,elog1_el,id_dss)
select ID_drd,id_fc,elog1_el,id_dss
from new_name;
end

SQL Anywhere Developer Community:
http://www.sybase.com/developer/libr...ere-techcorner


SQL Anywhere Blog Center: http://www.sybase.com/sqlanyblogs




Zoe wrote:

You are fetching into a different set of variables...

....than your are using in your INSERT VALUES list...

AFAIK the column values will all be NULL.

Breck

On Tue, 15 Sep 2009 04:12:35 -0700 (PDT), Zoe
<laurakeaveney (AT) gmail (DOT) com> wrote:

--
Breck Carter http://sqlanywhere.blogspot.com/

RisingRoad SQL Anywhere and MobiLink Professional Services
breck.carter (AT) risingroad (DOT) com

Also my Trigger syntax was wrong - missing two ";"
Works perfect:

ALTER TRIGGER "HoldingTrig".HoldingTrig after insert order 4 on
DBA.TBLRECORD
referencing new as new_name
for each statement
begin
insert into tblholding_th (ID_drd,id_fc,elog1_el,id_dss)
select ID_drd,id_fc,elog1_el,id_dss
from new_name;
end



Thank you for your help!



On Sep 15, 2:51*pm, "Breck Carter [TeamSybase]"
<NOSPAM__breck.car... (AT) gmail (DOT) com> wrote:

Why would the owner of the trigger be
"HoldingTrig" when the owner of the
table is "DBA"?

And .... does permissions come into play here?

O\W ...

If only in the spirit of ISIS (the god of simplicity) and
as a basis for further analysis .... Try this one instead

CREATE TRIGGER "DBA".TAI_RL_HoldingryTrig
after insert order 4 on DBA.TBLRECORD
referencing new as new_row for each ROW
begin
insert into tblholding_th( ID_drd,id_fc,elog1_el,id_dss)
values
(new_row.ID_drd,
new_row.id_fc,
new_row.elog1_el,
new_row.id_dss) ;
end;

As a row level trigger it should be able to do
the operation 1 row at a time. That should
(assuming I haven't made any mistakes)
just work as long as there is a commit
involved and everything else lines up (data
types, column names, etc etc)

The above should help simplify the question
(significantly) if that fails. And even if it fails
the procedure debugger should be able to drill
into this to help see where it is failing.

If the above works there likely isn't any
requirement to use a statement level
trigger unless you typically do massive
inserts from selects. And if you do
happen to be doing that (on a frequent
enough basis to actually benefit from
the savings in some meaningful way)
then this example may help:

CREATE TRIGGER "DBA".TAI_SL_HoldingryTrig
after insert order 4 on DBA.TBLRECORD
referencing new as new_table for each STATEMENT
begin
insert into tblholding_th( ID_drd,id_fc,elog1_el,id_dss)
select ID_drd,id_fc,elog1_el,id_dss
from new_table;
-- note: in T/SQL tradition one would normally joing
-- new_table (in t/sql "inserted") to
end;


Again, if you needed the cursor to add logic to this
then your trigger looks good, but looks (like with
magazine covers and superficially pretty 'things')
is often deceiving. Debugging it will be your
next step there [the green bug button in Sybase
Central is your starting point there]


"Zoe" <laurakeaveney (AT) gmail (DOT) com> wrote